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

Private Sub TextBox1_Change()

End Sub

Thanks for any help you can provide.
J

.

Option Explicit

Private Sub UserForm_Initialize()
    TextBox1.Text = Application.WorksheetFunction.Max(Range("A:A"))
End Sub

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?

Option Explicit

Private Sub UserForm_Initialize()
    TextBox1.Text = Application.WorksheetFunction.Max(Range("A:A"))
End Sub

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

Private Sub UserForm_Initialize()
LoadComboBox 
ClearForm
End Sub

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

Option Explicit

Private Sub UserForm_Initialize()
LoadComboBox 
ClearForm
Me.TextBox1.Value = Sheet2.Cells(1, 39).Value
End Sub

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 :

Private Sub TextBox1_Change()
TextBox1 = Cells(1, 39)
End Sub

Private Sub UserForm_Initialize()
TextBox1 = Cells(1, 39)
LoadComboBox
ClearForm
End Sub

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 !

Private Sub UserForm_Initialize()
ClearForm
TextBox1 = Cells(1, 39)
LoadComboBox

End Sub

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.

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.

Me.Reg3 = DateValue(Me.Reg3)
Me.Reg4 = DateValue(Me.Reg4)
Me.Reg14 = DateValue(Me.Reg14)

.
Glad to help ...

You are welcome.

Delete the three rows in your post#15

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

Private Sub cmdADD_Click()
If Me.Reg1 = "" Then
MsgBox ("No Refrence Number Entered"), vbOKOnly + vbInformation, "Error"
Me.Reg1.SetFocus
Exit Sub
End If
sc = Me.ComboBox1.ListCount + 1
chk = Me.Reg1.Value
Set ws = Sheet2
Set tbl = ws.ListObjects("Table1")
With tbl
For m = 1 To sc
If .Range(m, 1) = chk Then
response = MsgBox("Duplicate Item Number - Is this a different " & chk & " ?", vbYesNo, "Duplicate")
If response = vbNo Then
ClearForm
Exit Sub
End If
End If
Next m
Application.ScreenUpdating = False
Set newrow = tbl.ListRows.Add
With newrow
For x = 1 To 28
.Range(x) = Me("Reg" & x).Value
Next x

'**************************** additional code ********************
If IsDate(Controls("Reg3").Text) Then
.Range(3) = DateValue(Controls("Reg3").Text)
Else
.Range(3) = ""
End If
If IsDate(Controls("Reg4").Text) Then
.Range(4) = DateValue(Controls("Reg4").Text)
Else
.Range(4) = ""
End If
If IsDate(Controls("Reg14").Text) Then
.Range(14) = DateValue(Controls("Reg14").Text)
Else
.Range(14) = ""
End If
'****************************************************************

End With
End With
Set sortcolumn = Range("Table1[Full Name]")
With tbl.Sort
   .SortFields.Clear
   .SortFields.Add Key:=sortcolumn, SortOn:=xlSortOnValues, Order:=xlAscending
   .Header = xlYes
   .Apply
End With
ClearForm
LoadComboBox
Application.ScreenUpdating = True
MsgBox ("Details Saved"), vbOKOnly + vbInformation, "SAVED"
End Sub

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..