Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Cell and open UserForm
I found this information that gets me started:
Use the selection change event. '====== Right click on the sheet tab and select view code. In the left dropdown at the top of the module select Worksheet in the right dropdown at the top of the module select SelectionChange this will place the declaration in the module. Private Sub Worksheet_SelectionChange(ByVal Target As Range) End Sub you can put your code he Private Sub Worksheet_SelectionChange(ByVal Target As Range) if Target.Address = "$A$1" then Userform1.Show End if End Sub '====== Thanks Tom Ogilvy (From URL: http://www.microsoft.com/office/comm...af4&sloc=en-us ) What I would like to know is how can this be dynamic? Example: If a user deletes a row, how or can the above event still function? -- Regards VBA.Noob.Confused XP Pro Office 2007 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Cell and open UserForm
I'm not quite sure what you're asking. A worksheet will always have a cell
A1, and your code will always run if cell A1 (and only A1) is selected. You might try using a defined name. Select your target cell on the worksheet and give it a name. You do this by clicking on the name box (the box above the "1" row number and the "A" column letter, to the left of the formula bar) and typing in a name (no spaces allowed). Then, test the Name of the Target cell. For example, the following code will show UserForm1 is the user clicks on the cell with a Name of "TheCell". Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim S As String On Error Resume Next S = Target.Name.Name On Error GoTo 0 If S = "TheCell" Then UserForm1.Show Else ' do nothing End If End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Rick S." wrote in message ... I found this information that gets me started: Use the selection change event. '====== Right click on the sheet tab and select view code. In the left dropdown at the top of the module select Worksheet in the right dropdown at the top of the module select SelectionChange this will place the declaration in the module. Private Sub Worksheet_SelectionChange(ByVal Target As Range) End Sub you can put your code he Private Sub Worksheet_SelectionChange(ByVal Target As Range) if Target.Address = "$A$1" then Userform1.Show End if End Sub '====== Thanks Tom Ogilvy (From URL: http://www.microsoft.com/office/comm...af4&sloc=en-us ) What I would like to know is how can this be dynamic? Example: If a user deletes a row, how or can the above event still function? -- Regards VBA.Noob.Confused XP Pro Office 2007 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Cell and open UserForm
For not quite understanding my question, you hit it right on the head!
Works perfect, whether I add/delete rows and or columns. I'd hug you, but, I'm a guy, your a guy, and well, you know....It would be wierd. Happy Holidays Chip! ;) -- Regards VBA.Noob.Confused XP Pro Office 2007 "Chip Pearson" wrote: I'm not quite sure what you're asking. A worksheet will always have a cell A1, and your code will always run if cell A1 (and only A1) is selected. You might try using a defined name. Select your target cell on the worksheet and give it a name. You do this by clicking on the name box (the box above the "1" row number and the "A" column letter, to the left of the formula bar) and typing in a name (no spaces allowed). Then, test the Name of the Target cell. For example, the following code will show UserForm1 is the user clicks on the cell with a Name of "TheCell". Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim S As String On Error Resume Next S = Target.Name.Name On Error GoTo 0 If S = "TheCell" Then UserForm1.Show Else ' do nothing End If End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Rick S." wrote in message ... I found this information that gets me started: Use the selection change event. '====== Right click on the sheet tab and select view code. In the left dropdown at the top of the module select Worksheet in the right dropdown at the top of the module select SelectionChange this will place the declaration in the module. Private Sub Worksheet_SelectionChange(ByVal Target As Range) End Sub you can put your code he Private Sub Worksheet_SelectionChange(ByVal Target As Range) if Target.Address = "$A$1" then Userform1.Show End if End Sub '====== Thanks Tom Ogilvy (From URL: http://www.microsoft.com/office/comm...af4&sloc=en-us ) What I would like to know is how can this be dynamic? Example: If a user deletes a row, how or can the above event still function? -- Regards VBA.Noob.Confused XP Pro Office 2007 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you make a userform open automatically when you open excel? | Excel Worksheet Functions | |||
Userform, select entire field | Excel Programming | |||
select open webpage, copy information to open worksheet | Excel Programming | |||
select [a1]cell when hotkey(ctrl+q) pressed, after userform load | Excel Programming | |||
Userform, multipage, select a tab | Excel Programming |