VBA code message box dilemma......

[mattbird]

Hi,

How can I make the following code return the message "Now click on Update Stats" if a date is entered into the cell and if a wrong entry is entered i.e. texted, another message will be displayed saying "Incorrect entry, please re-enter the assessment date".

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("R15:R33")) Is Nothing Then Exit Sub
If Target.Value = "" Then Exit Sub
MsgBox "Now click on Update Stats."

End Sub

Thanks

Matt

 

[bertie]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Cells.Count > 1 Then Exit Sub
   If Intersect(Target, Me.Range("R15:R33")) Is Nothing Then Exit Sub


   If IsDate(Target.Value) = "" Then
      MsgBox "Now click on Update Stats."
   Else
      MsgBox "Incorrect entry, please re-enter the assessment date."
   End If


End Sub

 

[mattbird]

Thanks for the quick reply,

I get a run time-error 13 type mismach 'If Isdate(Target.Value) ="" Then' highlighted in yellow. This error happens with a date entered or mistake entered?

Matt

 

[Rick Rothstein]

Thanks for the quick reply,

I get a run time-error 13 type mismach 'If Isdate(Target.Value) ="" Then' highlighted in yellow. This error happens with a date entered or mistake entered?

IsDate returns a Boolean (True or False), so it cannot equal "". Change that line of code to this...

If IsDate(Target.Value) Then

 

[mattbird]

Rick,

Thanks works perfectly. would you be able to help me with another issue I am having with a message box? I am trying to put some instructions in one on how to use my database but you can only put so much in. So I was looking at creating a message box that has tabs in it ie 'Information', 'Instructions', 'View Stats' and 'Help'. The user can then click on each tab in the message box to get the info he/she need and the click the Ok button to exit. Is this possible and if so how would I create the code for it? I have look on the net and there is stuff slightly like what I am trying to do but I don't fully understand the code as I am still learning....

Your help would be greatly appreciated

Matt

 

[Andre Uys]

Matt, the msgbox function does not support the functionality you desire, you can either create a custom form to display this type of data or you can use use vbcrlf to break out the text in your msgbox and add a little more structure, you can display approx 1024 characters in a message box. This link might give you some ideas.

Here is an example of how to use vbcrlf to add a carriage return-line feed to your text to break it up.

Dim msg As String
msg = "There was an error....." & vbCrLf
msg = msg & vbCrLf & "Information:" & vbCrLf
msg = msg & "Text about information goes here" & vbCrLf
msg = msg & "More text......" & vbCrLf

msg = msg & vbCrLf & "Instructions:" & vbCrLf
msg = msg & "Instructions goes here" & vbCrLf
msg = msg & "More text......" & vbCrLf

MsgBox msg, vbOKOnly, "Custom Message"

 

[Rick Rothstein]

Rick,

Thanks works perfectly. would you be able to help me with another issue I am having with a message box? I am trying to put some instructions in one on how to use my database but you can only put so much in. So I was looking at creating a message box that has tabs in it ie 'Information', 'Instructions', 'View Stats' and 'Help'. The user can then click on each tab in the message box to get the info he/she need and the click the Ok button to exit. Is this possible and if so how would I create the code for it? I have look on the net and there is stuff slightly like what I am trying to do but I don't fully understand the code as I am still learning....

What you are asking for is well beyond what MessageBoxes were designed for. You could create a UserForm that has either a TabStrip or a MultiPage ActiveX control on it with TextBoxes containing your information, but I am thinking that may be beyond your current VBA development level right now. A rather simple alternative would be to name four tabs "Information", "Instructions", "View Stats" and "Help" and then put whatever information you want on those worksheets. That way, you can use shapes, labels and cell values to dress up your information's presentation in any one of many, many ways. Also, the information would be at hand for the user whenever they needed it, and once they felt they knew it well enough, they could hide the sheets (leaving them available to be unhidden if the need arose to refer to them again at a late date).

 

[al_b_cnu]

Hi, Why don't you just use Data Validation?

 

[mattbird]

Hi,

Thanks for everyone replies. I have designed a user form using the mult page option and it is working. The only issue is when I open it with my button I have noticed the user can edit the text, how do I prevent this? I would like it as a read only. the only thing a user should be able to do is click on the tabs or close user form. I have written the following code to open and close:

Open

Sub BCMDatabase_Information_Oval45_Click()
UserForm1.Show


End Sub

Close

Private Sub CommandButton1_Click()
Unload UserForm1


End Sub


Does anyone now how to lock the user form?

Matt

 

[al_b_cnu]

Hi Matt,

I presume you're using Text Boxes, which are designed for user input. Either set Enabled property to false or replace the textboxes with labels