Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Double Click" macro?
Is there a way to create a macro that if you double click on a cell or row it
will activate a worksheet. Example would be: If cell A1 is double clicked, or at least activated and "submitted" using a Command Button, the result would be: Worksheets ("12").Activate See I have 94 rows of assembly descriptions on a worksheet [Worksheets("Start")], and 94 other worksheets that have a Bill Of Materials. I would like to have a person be able to highlight a cell or row and it brings up the correct BOM. A double click would be easiest on the end user but I think an active row or cell with a Command Button is more achievable. Any help is greatly appreciated. Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Double Click" macro?
Here is a double-click example. Once installed, if you double click on B9 it
takes you to another sheet: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Set t = Target Set r = Range("B9") If Intersect(t, r) Is Nothing Then Exit Sub Sheets("Sheet1").Activate End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200907 "Cerberus" wrote: Is there a way to create a macro that if you double click on a cell or row it will activate a worksheet. Example would be: If cell A1 is double clicked, or at least activated and "submitted" using a Command Button, the result would be: Worksheets ("12").Activate See I have 94 rows of assembly descriptions on a worksheet [Worksheets("Start")], and 94 other worksheets that have a Bill Of Materials. I would like to have a person be able to highlight a cell or row and it brings up the correct BOM. A double click would be easiest on the end user but I think an active row or cell with a Command Button is more achievable. Any help is greatly appreciated. Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Double Click" macro?
Great, thank you!
"Gary''s Student" wrote: Here is a double-click example. Once installed, if you double click on B9 it takes you to another sheet: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Set t = Target Set r = Range("B9") If Intersect(t, r) Is Nothing Then Exit Sub Sheets("Sheet1").Activate End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200907 "Cerberus" wrote: Is there a way to create a macro that if you double click on a cell or row it will activate a worksheet. Example would be: If cell A1 is double clicked, or at least activated and "submitted" using a Command Button, the result would be: Worksheets ("12").Activate See I have 94 rows of assembly descriptions on a worksheet [Worksheets("Start")], and 94 other worksheets that have a Bill Of Materials. I would like to have a person be able to highlight a cell or row and it brings up the correct BOM. A double click would be easiest on the end user but I think an active row or cell with a Command Button is more achievable. Any help is greatly appreciated. Thanks in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Double Click" macro?
You are very welcome, but also consider using a hyperlink to accomplish the
same thing. The hyperlink only needs a single click (you see I am very lazy!) -- Gary''s Student - gsnu200907 "Cerberus" wrote: Great, thank you! "Gary''s Student" wrote: Here is a double-click example. Once installed, if you double click on B9 it takes you to another sheet: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Set t = Target Set r = Range("B9") If Intersect(t, r) Is Nothing Then Exit Sub Sheets("Sheet1").Activate End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200907 "Cerberus" wrote: Is there a way to create a macro that if you double click on a cell or row it will activate a worksheet. Example would be: If cell A1 is double clicked, or at least activated and "submitted" using a Command Button, the result would be: Worksheets ("12").Activate See I have 94 rows of assembly descriptions on a worksheet [Worksheets("Start")], and 94 other worksheets that have a Bill Of Materials. I would like to have a person be able to highlight a cell or row and it brings up the correct BOM. A double click would be easiest on the end user but I think an active row or cell with a Command Button is more achievable. Any help is greatly appreciated. Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Double click a blank cell to enact "ctrl + ;" | Excel Worksheet Functions | |||
Excel: can "go to adjacent empty cell" double-click be disabled? | Excel Discussion (Misc queries) | |||
excel status bar says "double-click to edit PBrush" | Excel Worksheet Functions | |||
status bar says "double-click to edit pbrush" | Excel Discussion (Misc queries) | |||
I have to double click a cell for the "text" format to take | Excel Discussion (Misc queries) |