Controlling Entire Worksheet with VBA Function

[Premanand Sethuraman]

Dear All,
I am making a program in which I want to control the Worksheet with the
backend VBA Function (Change by Val as Target).
I just want to pop up an Input message in a Cell Say A2 like " Please input
the boy's name" . Cell A1 will have drop down list . If the user select a
data from the drop down list, the above input message should come. If the
user select some other data from the cell A1 (drop down list), another input
message should be popped up in the Cell A2 like "Please input the girl's
name",
Will u please help me how to do that in "Change by Val as Target" Function?

Regards,
Premanand.

 

[Joel]

Private Sub Worksheet_Change(ByVal Target As Range)

For Each cell In Target
Select Case cell.Address

Case "$A$1"

Case "$A$2"

End Select
Next cell
End Sub

 

[Premanand Sethuraman]

Dear Joel,
Thanks for the reply.
But I want to know how to pop up the message in a particular cell with the
help of data validation.
This is the coding I wrote but I am not getting any message as per I gave
below...

Private Sub Worksheet_Change(ByVal h1 As Range)
Application.ScreenUpdating = False
If h1.Row = 13 And h1.Column <= 7 Then
If h1.Text = "Male Student" Then
With ActiveSheet.Range(Cells(14, h1.Column)).Validation
..InputMessage = " Please enter Boy's Name"
.ShowInput = True
.ShowError = False
End With
Else
With ActiveSheet.Range(Cells(14, h1.Column)).Validation
..InputMessage = " Please enter girl's Name"
.ShowInput = True
.ShowError = False
End If
End If
End Sub

Please ssuggest me what I can do further.

Thanks
Prem.

 

[Joel]

Just write the string to the cell. As long as the string is a valid item it
will work

Sub Worksheet_Change(ByVal h1 As Range)
Application.ScreenUpdating = False
If h1.Row = 13 And h1.Column <= 7 Then
If h1.Text = "Male Student" Then
ActiveSheet.Range(Cells(14, h1.Column)) = " Please enter Boy's Name"
Else
ActiveSheet.Range(Cells(14, h1.Column)) = " Please enter Girl's Name"
End If
End if
End Sub

 

[Premanand Sethuraman]

Joel,
I am sorry I didn't explain you clearly.
I want as a message which will pop up in the cell (of 14 th row).I don't
want the sentence in the Cell.
Once the user click (or) enter into the cell, message will pop up at the
side of the cell (which we will usually do manually like
Data->Validation->Input message->"Please enter the male's name").
I want to do the above function with the help of VB Coding once if the user
select " Male Studenet" in the Previous cell (i.e 13 th row).
Kindly suggest.

Regards,
Prem.

 

[Joel]

You just need to select the cell. I made some changes to the code. The
error was being cause by the way you were addressing the cell. Not 10-0%
sure whatt you wanted to do with Range(Cells(14, h1.Column)) which is not a
valid cell reference.

Private Sub Worksheet_Change(ByVal h1 As Range)
Application.ScreenUpdating = False
If h1.Row = 13 And h1.Column <= 7 Then
With ActiveSheet.Cells(14, h1.Column).Validation
If h1.Text = "Male Student" Then
.InputMessage = " Please enter Boy's Name"
Else
.InputMessage = " Please enter girl's Name"
End If
End With
ActiveSheet.Cells(14, h1.Column).Select
End If
End Sub

 

[Premanand Sethuraman]

Thank you so much Joel,
Finally It's working based on the coding given by you.
Thanks for your sugggestions.

Regards,
Premanand.S