Userforms - textbox data validation & MsgBox problem

lionelnz

Well-known Member
Joined
Apr 6, 2006
Messages
568
Hi there.

I am trying to develop a relatively simple userform where user enters (at the moment) a number code & date ("dd/mm").

I want ensure that the correct data goes in as it links back to a SS that carries out a range of complex calculations in teh worksheet & then displays the update data back to user.

I am having problems making sure that there is data in teh textbox, & that it in box1 it a number between 50 & 20600 & in Box2 that it is a date format of "dd/mm".

I cannot get the messagebox to work despite googling & having 2 VBA books as well!

I also want to make sure that the output boxes(gets data from sheet to display) cannot be changed & sent back to sheet as the data is based on complex formulas.

Hers is what I have -

Code:
Private Sub btnOK_Click()
'To enter US data & show uodated calculations


Dim USNo As String 'From US # Textbox
Dim DateAchvd As String 'From US date achvd textbox
USNo = txtUSNo.Value
DateAchvd = txtDateAchvd.Value
'Ensure US No is enetred
If Not USNo <> "" And USNo > 56 And USNo < 20600 Then
ResultUS = MsgBox("Please enter valid US Number.", vbOKOnly)
txtUSNo.Value = ""
txtUSNo.SetFocus

'Ensure date "dd/mm" is entered
ElseIf Not txtDateAchvd.Value <> " " Then
ResultDate = MsgBox("Please enter valid Date Format", vbOKOnly)
txtDateAchvd.Value = ""
txtDateAchvd.SetFocus
End If
'Make sure US Prgrss is active
Sheets("US Prgss").Activate

'Find the cell with the US selected
Range("T6:CZ6").Select
    Selection.Find(What:=USNo, After:=ActiveCell).Activate
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = DateAchvd
    'ActiveCell.Offset(1, 0).Select
    'ActiveCell.FormulaR1C1 = DateAchvd
  
    
    
txtUSNo.Value = ""
txtDateAchvd.Value = ""
ActiveCell.Range("C7").Activate
End Sub

BTW I am using XL 2000 with VBA(VB)6.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi

try to convert txtbox(String) to Number by

Val(USNo)

so that you can compare...
 

lionelnz

Well-known Member
Joined
Apr 6, 2006
Messages
568
Thanks.

I have changed it but the probem I have is this

Rich (BB code):
MsgBox("Please enter valid US Number.", vbOKOnly) = vbOK

Comes up with error message "LH side must have variant or object"

So I will try again.

Chhers anyway

Here it is again

Private Sub btnOK_Click()
'To enter US data & show uodated calculations


Rich (BB code):
Dim USNo As Integer 'From US # Textbox
Dim DateAchvd As String 'From US date achvd textbox
USNo = Int(txtUSNo.Value)
DateAchvd = txtDateAchvd.Value
'Ensure US No is enetred
If Not USNo <> "" And USNo > 56 And USNo < 20600 Then :evil: ResultUS = MsgBox("Please enter valid US Number.", vbOKOnly)
    If ResultUS = vbOK Then
    txtUSNo.Value = ""
    txtUSNo.SetFocus
    End If

'Ensure date "dd/mm" is entered
ElseIf Not txtDateAchvd.Value <> " " Then
ResultDate = MsgBox("Please enter valid Date Format", vbOKOnly)
    If ResultDate = vbOK Then
    txtDateAchvd.Value = ""
    txtDateAchvd.SetFocus
    End If
End If
'Make sure US Prgrss is active
Sheets("US Prgss").Activate

'Find the cell with the US selected
Range("T6:CZ6").Select
    Selection.Find(What:=USNo, After:=ActiveCell).Activate
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = DateAchvd
    'ActiveCell.Offset(1, 0).Select
    'ActiveCell.FormulaR1C1 = DateAchvd
   
txtUSNo.Value = ""
txtDateAchvd.Value = ""
ActiveCell.Range("C7").Activate
End Sub

I get an error message "Mismatch error 13" on that line of code that is bold with :devilish: !!!!!

So no doubt I would get on the second MsgBox too!

Can't belive that this is so difficult!!!

Cheers
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
What do you expect from the MsgBox with OKOnly ?

Change to vbYesNo

If vbYes = MsgBox(......,vbYesNo) Then...
 

lionelnz

Well-known Member
Joined
Apr 6, 2006
Messages
568

ADVERTISEMENT

Textbox validation

What do you expect from the MsgBox with OKOnly ?.

Itried using
Code:
MsgBox("Please enter valid US Number.", vbOKOnly)

& I kept getting error msg "Must have variant or object on leftside of statement." I have Walkenbachs PowerProgramminf for Excel2003 as wellas a VBA 6 book & VB6!

I have not been able toresolve the problem, I can write severalnested if functions but I am sure there is a simpler way.

However I will try what you suggest!

Lionel
 

lionelnz

Well-known Member
Joined
Apr 6, 2006
Messages
568
What do you expect from the MsgBox with OKOnly ?

Change to vbYesNo

If vbYes = MsgBox(......,vbYesNo) Then...

Hasve done this & when tested with data outside the range came up with the dreaded yellow debug line saying "Error Mismatch 13".
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Which line?

And because you don't have Else caluse, it goes next line of the code anyway.

What did you want to do when "No" or other than "OK" was returned from the msgbox?
 

lionelnz

Well-known Member
Joined
Apr 6, 2006
Messages
568
Yeah I see your point.pf the Else part.

Just used toin simplemacros not having toworry about Else as it was ending anyway but this is a bit different.

I will keep at it. Althoplaying around with the code I have seemed tolost the part of actually entering data intothe cell!!! :devilish:
 

Forum statistics

Threads
1,137,298
Messages
5,680,678
Members
419,924
Latest member
Dhamodharan992

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top