Adding a value textbox to a userform

Hi Forum
Wonder if anyone could help me adding a textbox to a userform that will show the highest current number in Row A,
I can add the textbox ok and can create a formula to show the highest number but not getting anything to show on the userform textbox. The Private Sub I created is as below so looking for a formula that will make it pick up the amount sitting in Row AM1 of worksheet2

Please Login or Register  to view this content.

Thanks for any help you can provide.
J

.

Please Login or Register  to view this content.

Late edit: sorry Norie ... I was posting after you.

Hi Norie and Logit
Thanks a lot for both your inputs, I tried loading the code below into UserForm Initialize but it created an error "Compile Error: Ambiguous name detected: UserForm_Initialize" Am I missing something obvious?

Please Login or Register  to view this content.

Cheers

.
"UserForm" should be named "UserForm1" if you did not name it something else and you only have one userform.

Hi, I only have one UserForm which is called UserForm1, although I already use Initialize in the code below to load a Combo "LoadComboBox". Would that prevent me running another? Thanks

Please Login or Register  to view this content.

You can only have one initialise event per userform.
the third row in code below reads the cell AM1 in sheet2
see demo file

Please Login or Register  to view this content.

Hi Torachan
Demo does exactly as I need it to but if I cant use UserForm1 how do I get it on the same userform?
Thought I would add a sample copy of the userform im working with to hopefully explain better.
On top left corner is Next Ref No. which I need to pick up from cell AM1, formula in this cell is =MAX(A:A)+1
to add one to show next available no. I you can think of anything that would be great.

Another thing I am trying to change is the date format on rows C,D and N which are mm/dd/yyyy where I need dd/mm/yyyy if you
know where this can be changed. I changed it in the worksheet but userform is not changing.

Thanks
J

Rather than search for new max number on initialisation I have put the code on a new button.
The reason for this is that on add new client the form clears including new number-with the seperate button it can be recalled at any time - there are no formula on the sheet now.
Also add DateValue to 3 items in the combobox search code.
No matter what you name your userform the UserForm initialization is always named just UserForm and will not be recognised as anything else.

.
The loading (initializing) of the UserForm should suffice for updating the value of TextBox1 ... but ... this is confusing because
what should work ... isn't.

I was able to have the value from AM1 populate TextBox1 with the following edit of the code :

Please Login or Register  to view this content.

However, the above is not standard coding from my experience.

Comments ?

@Logit, indeed it would and does, however the ClearForm sub immediately clears the textbox. hence your textbox1_change event then refilled the text box
If the line TextBox1=cells(1,39) had been put after the ClearForm event there then would be no need for the TextBox1_Change event.
Also I was trying to emphasize that the initialization does not use the form name - along with activate they are two process that are automatically given priority on opening a userform.
The reason I committed it to code and a button is personal preference - I hate dribs and drabs of formula in a sheet when using VBA.
torachan

.
I overlooked the ClearForm call. My oops !

Please Login or Register  to view this content.

Interesting how the mind can focus on a single thing and miss the obvious. Duh !

Torachan that works perfectly as does the dating , many thanks.

Logit, Norie I really appreciate you both also looking at the for me, again thank you.

.
Glad to help ...

You are welcome.

Hi Torachan
I know said the tool works perfectly (as it does for current data) but when I load new data it shows an error Run Time Error '13': Type mismatch when i look at the ComboBox1_Change it highlights the datavalues below. Would you have any idea what I can change to allow new records to be entered? Thank you.

Please Login or Register  to view this content.

Delete the three rows in your post#15

paste the code below over the existing 'ADD' sub.

Please Login or Register  to view this content.

Format the three date columns as per format in clip.
Format the complete columns - not just the table content - the format needs to be in place ready to receive added data.

Attachment 703116

Thanks for the reply Torachan, sorry I didn't get back yesterday, I only got back on my PC today.
I think I follow what your are saying needs to be done but just before I try it, I can't open the attachment as it tells me to contact administrator, does it just show how I should format the three data columns? i.e. is it just going through Custom and selecting dd/mm/yyyy?

goto 'Date' and formatCapture.JPG

Tried a few different ways to make the adjustments you suggested Torachan but not having any luck. I know its a lot to ask but is there any chance you could make the suggested changes to the original "Sample Form(tora(v1)).xlsm‎" you sent me and resend? Just not sure I'm going to get it running and its so near what I need.

Its for a brain damage charity to load client data as their Access tool crashed, managed to pull their data and trying build something simple for them as a replacement.

Thanks for your help..

attached file updated and working OK.
if you are pasting in data - paste values only - not formatting as this is likely to overwrite the date format.

Perfect... Thank you so much for your help with this..