Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button - On Click Event
Hello,
I have a worksheet with about 1,000 lines of data. I want to add a command button on each line, which when clicked will take me to a certain row in another tab of the workbook. When you click on the button, the code for the on click event takes you to the specific row which is referenced off to the side of that row. I think I know how to add the command buttons on each row by using a macro. My question, is there a way to add the code to each buttons click event through a macro or code so I do not have to type it in manually 1000 times? Hope I am explaining this correctly. Any help would be greatly appreciated!! Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button - On Click Event
I would not put 1000 buttons on a worksheet.
You would probably be back the following week wondering why your file was corrupted. Hyperlink formulas are an alternative; also using the double click event of the worksheet is a good way to go. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "sharonm" wrote in message Hello, I have a worksheet with about 1,000 lines of data. I want to add a command button on each line, which when clicked will take me to a certain row in another tab of the workbook. When you click on the button, the code for the on click event takes you to the specific row which is referenced off to the side of that row. I think I know how to add the command buttons on each row by using a macro. My question, is there a way to add the code to each buttons click event through a macro or code so I do not have to type it in manually 1000 times? Hope I am explaining this correctly. Any help would be greatly appreciated!! Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button - On Click Event
sharonm,
I second Jim's advice. So, for example, in Sheet1 you could have the cells in Column A function as your buttons, in that each time you double-click one, it will trigger the activation of the same row on Sheet2. To do so, paste this code into the ThisWorkbook module of your workbook. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'If the double-click "Target" is in Column A If Not Intersect(Target, Columns(1)) Is Nothing Then 'Cancel the normal double-click action 'so that you don't enter the "button" cell. Cancel = True With ThisWorkbook.Worksheets("Sheet2") .Activate 'Select the same row in Sheet2 .Rows(Target.Row).Select End With End If End Sub hth, Doug "Jim Cone" wrote in message ... I would not put 1000 buttons on a worksheet. You would probably be back the following week wondering why your file was corrupted. Hyperlink formulas are an alternative; also using the double click event of the worksheet is a good way to go. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "sharonm" wrote in message Hello, I have a worksheet with about 1,000 lines of data. I want to add a command button on each line, which when clicked will take me to a certain row in another tab of the workbook. When you click on the button, the code for the on click event takes you to the specific row which is referenced off to the side of that row. I think I know how to add the command buttons on each row by using a macro. My question, is there a way to add the code to each buttons click event through a macro or code so I do not have to type it in manually 1000 times? Hope I am explaining this correctly. Any help would be greatly appreciated!! Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button - On Click Event
I wouldn't use 1000 buttons, either.
But if you used the button from the Forms Toolbar (not the control toolbox toolbar), you could assign the same macro to each button. You could use the location of the button that was clicked to get the row number you wanted. Have you thought about putting one button on the worksheet--maybe in row 1 and freeze row 1 so that it's always visible. Then using the activecell's row to determine what to do? Or even a toolbar that floats over the worksheet (and still uses the activecell's location). sharonm wrote: Hello, I have a worksheet with about 1,000 lines of data. I want to add a command button on each line, which when clicked will take me to a certain row in another tab of the workbook. When you click on the button, the code for the on click event takes you to the specific row which is referenced off to the side of that row. I think I know how to add the command buttons on each row by using a macro. My question, is there a way to add the code to each buttons click event through a macro or code so I do not have to type it in manually 1000 times? Hope I am explaining this correctly. Any help would be greatly appreciated!! Thanks. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
On click button event | Excel Worksheet Functions | |||
why do i have to click my 2nd command button twice | Excel Programming | |||
event with commandbar button right-click? | Excel Programming | |||
Automate a button click event | Excel Programming | |||
Running command button click event code | Excel Programming |