Posts by Mark UK

Hello,

With reference to the attached/uploaded example... For each supplier I wish to show a "Supplier Final Weighted Score" (SFWS) for each row of that Supplier. The SFWS should be the unique sum of the "Section Final Weighted Score" by Supplier. In the example Comp1 should total 42% (Section A 9% + Section B 15% + Section C 9% + Section E 6% + Section F 3%), but I've got it stuck on too much uniqueness and it's not adding in one of the 9%'s, resulting in SFWS = 33% rather than 42%.

Evaluation sheet.xlsx

That's probably a poor explanation of the problem - apologies - but my brain just can't figure it out.

Any help appreciated.

Kind regards,

Mark

Re: Calculate time duration in minutes

Ah! Thank you very much Luke! Exactly what I was after. And having worked through your formula I understand how it works.

Very much appreciated.

Regards,

Mark

Hello,

I am trying to calculate the time duration between a "From" and "To" time which aren't in an Excel time format. There is an example data sheet attached, with the format exactly as I have to work with. Where it says "From 9.30" that is 09:30. "To 10.00" is to 10:00. A difference of 30 minutes. If I type "09:30" and "10:00" into cells and simply subtract, I can get "30", but I have thousands of rows of times in this format - and I cannot fathom out the steps to come up with a formula.

Any help greatly appreciated - and apologies if the title or this bit doesn't accurately describe to you what I'm after!

Thanks and regards,

Mark

Re: Best userform control to accept a Calendar control input value

Well I've got it all working (apparently) - thank you for your help and advice. I did find that I had to format the date for the textbox to "dd/mm/yyyy" otherwise it appeared in the textbox (from the Calendar) in the format "mm/dd/yyyy" - although CDate fixed that when saving to the sheet.

Many thanks.

Re: Best userform control to accept a Calendar control input value

Thank you, Rory. Just one more question if I may, before I go changing my code... Do I need to set a "Format" for the textbox (i.e. "dd/mm/yyyy") or will the Calendar control pick up the correct date format automatically from my regional settings (or wherever) and pass that format to the sheet cell via what is in the userform textbox as I want? Is that something the CDate conversion does?

Re: Best userform control to accept a Calendar control input value

Ah! So it's the fact of showing the date - from the Calendar - in a textbox on the userform and THEN saving that textbox's contents to the sheet that causes the "problem"? And if I just picked a date from the Calendar and passed that straight away to the sheet then it wouldn't matter? Is that a "better" way of doing it? Though that would confuse users I suppose - as they'd see no date in the textbox on the form.

I think I understand - thank you.

Re: Best userform control to accept a Calendar control input value

Thanks for that Rory - much appreciated. I'll give that a go now. (Also good to know I don't have to change all the textboxes I set up to accept a date to comboboxes!)

As a matter of interest though, why is it necessary to use the CDate conversion on a variable that has already been dimmed as Date?

Hello,

I have a userform with calendar controls on it (both on a textbox "Enter" and combobox "Mousedown" event). Which is the best control to use to accept the date as clicked on the Calendar - a textbox or a combobox? Or does it make no difference? It's just I'm not getting the desired date value formatted correctly when I save the record to a worksheet and was wondering if the problem lies with a date being a number to Excel and the textbox default data type being (funnily enough) text. Or should I look at what else I might have coded incorrectly?

Picking a date from the calendar puts the date into the text or combo box on the form in the format I want (English dd-mm-yyyy, which matches my Windows regional settings), but when the form's "Save to worksheet" button is clicked, the date isn't formatted "correctly" on the sheet.

I may be old, but I have a good head of hair - albeit it's in tufts at the moment! Any advice appreciated.

Thanks and regards,

Mark

Re: Re-populate multipage userform by searching on entries in two textboxes

Job's a good un, I'd say. Thank you very much for taking the time to help. Your solution has made me look in different ways at problems/requirements - as well as showing me new ways of achieving results. I hope the users are as appreciative! The final date thing works like a charm too - and neatly rounds it off. I've a few stages left to do in this project, but this job recording is a wrap. I hope!

Thanks again - and I hope you get vengeance for your bike! I've got 2 in me shed if you're as handy with spanners as you are with vba!

Cheers.

Re: Re-populate multipage userform by searching on entries in two textboxes

I've learnt so much from your example code - that's if my aged brain can remember it all! - so thank you very much. I've just about finished making my changes and coding up (and changing that niggly use of "Jub" instead of "Job" - figuring out that error was fun!) There were a couple of other problems "in the translation", so to speak, but I've fixed them it seems. One thing that I'm still having trouble with is putting a date from the form onto the sheet, but I suppose that isn't to do with this thread title and if I describe it I'll be in trouble, so I'd better not!

As a general point, why would a conversion function used on a textbox provoke an error if the textbox was empty? Is it a property of the text box control and do Ineed to set something specific for each textbox depending on what they are used for recording?

Re: Re-populate multipage userform by searching on entries in two textboxes

Thanks.

It's all coming along quite nicely I think. I've done a few changes to what I'd already had after looking at your example. I liked the idea of the Mode and how your code worked with that route. So I've rejigged my form and pages and am now going through putting in the references to put the data on the sheet and vice versa. The actual form has up to 68 pieces of data - how I wish it was just Data1 and Data2 as in my example!

One question - how does the hidden combo box (cboRow?) work? It's not "real", just a variable stored in memory?

Regards

Mark

Re: Re-populate multipage userform by searching on entries in two textboxes

Good morning. I'll be looking at this today and appreciate your offer of follow-up questions. I'll have a go at tailoring it to exactly what I'm doing (trying to do!) myself as I can't impose the whole thing on you - but I'm very grateful for your offer of help and will no doubt take you up on it!

Hope you have an ache and pain free day!

Re: Re-populate multipage userform by searching on entries in two textboxes

Oh yes, I can work with that! Mebbies not this late at night, but tomorrow is another day!

Thank you ever so much.

Re: Re-populate multipage userform by searching on entries in two textboxes

That's great - much appreciated.

To answer your question about quantities - there will be approx 500 job numbers in a year, but each will have varying numbers of suffixes, so requiring a row for each. Most jobs have less than 10, some have a figure in the teens and exceptional jobs might have 40 (but there probably won't be more than 1 or 2 of these a year). So rough guess a year's worth of records might be about 6000 rows.

Thanks once again.

Re: Re-populate multipage userform by searching on entries in two textboxes

A user will enter a job number, a job suffix, something in data1 and data2 - this will be added to the spreadsheet as a new record on the first available empty row.

To search for a record, a user will enter job number and suffix in those respective textboxes and then click search. This will then re-populate the form with that record. So, from the file that I posted, a user would enter job number 2000, job suffix s1, click search and the form would then populate Data1 with blue and Data2 with green (as well as the job number and suffix being in their own textboxes).

I hope that makes sense!

Re: Re-populate multipage userform by searching on entries in two textboxes

A job number can have several suffixes for different parts of the process, and each combination is a separate record. The way I was thinking was that the user would enter a job number and a suffix in those respective textboxes then Search for that combination; that would then be the record/row to return and repopulate the user form. So they wouldn't be selecting from a list, they'd be entering a job number and suffix that (hopefully) has been entered previously. The reason I was thinking textbox was because the list of records will grow to be quite large - and a combobox or listbox would get a bit unwieldy, yes?

How's your bike? And any serious damage to yourself - hope not.

Re: Re-populate multipage userform by searching on entries in two textboxes

Well it seems a bit harsh to ask a dying person to spend the last of their time on re-jigging something for me - but go on then, yes please!

It's very kind of you.

Re: Re-populate multipage userform by searching on entries in two textboxes

Certainly is a nice afternoon - be even nicer if I'd finished at work already!

Anyway - thanks very much for the code. It actually is what I want - the ability to add a new record and also to find an existing record.... in order to edit it! Sorry I didn't make that bit clear. So, your solution of 2 cmd buttons using this same code to either add or edit is spot on.

Unfortunately when I run it, it will successfully add a record, but not bring back an existing record. More than likely my fault!

Am I correct in thinking that the else statement in UpdateSheet uses the function to just identify the row in the database? And then I have to bring back the cell data to the form using offset type code - the reverse of the copy record to database code?

Regards,

Mark

Re: Re-populate multipage userform by searching on entries in two textboxes

Well I've deleted and re-attached the file (code posted earlier) but I still don't understand why I can see it all - even when I download it from the thread - and yet no-one else can. If it still doesn't work I'll try it later this afternoon from home (it is 12.52 pm here now).
I've also attached a screenshot of the form.
Thanks for sticking with this!

Re: Re-populate multipage userform by searching on entries in two textboxes

Here is the code for the Click to Add command button:

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Records")
'find first empty row in records database
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'copy record to database
ws.Cells(iRow, 1).Value = Me.txtJobNum.Value
ws.Cells(iRow, 2).Value = Me.txtJobSuffix.Value
ws.Cells(iRow, 3).Value = Me.txtData1.Value
ws.Cells(iRow, 4).Value = Me.txtData2.Value
Call UserForm_Initialize
End Sub

And the userform-initialize:

Private Sub UserForm_Initialize()
txtJobNum.Value = ""
txtJobSuffix.Value = ""
txtData1.Value = ""
txtData1.Value = ""
txtData2.Value = ""
txtJobNum.SetFocus
End Sub

I don't understand why the code and form aren't there in the file for you.