Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling Entire Worksheet with VBA Function
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" wrote: 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling Entire Worksheet with VBA Function
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" wrote: 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" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling Entire Worksheet with VBA Function
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" wrote: 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" wrote: 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" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling Entire Worksheet with VBA Function
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling Entire Worksheet with VBA Function
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling Entire Worksheet with VBA Function
Thank you so much Joel,
Finally It's working based on the coding given by you. Thanks for your sugggestions. Regards, Premanand.S "Joel" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to reference entire worksheet in new worksheet | Excel Worksheet Functions | |||
Is there a function to copy an entire worksheet? | Excel Worksheet Functions | |||
Controlling formating from a function | Excel Programming | |||
Controlling text in an Excel worksheet | Excel Worksheet Functions | |||
Controlling a cell in a worksheet | Excel Programming |