Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If active cell value is 1, run this macro
Hi There,
Situation - if you select the active cell then click a box, a macro runs based on the colour in your active cell. I would like to modify the VBA slightly (see below) to say if the value of the active cell is 1, then run macro 1x (call "1X"), if cell value is 2, then call Macro 2x, etc. This is what my existing Macro looks like: Public Sub Colour_views() Call unhide_cols Dim objWS As Excel.Worksheet Dim objCell As Excel.Range, objR As Excel.Range Dim i As Byte Dim J As Long Set objWS = Sheets("Master") ' Don't forget that wherever you're looking at, the selected cell needs to have a background ' color that matches the various "views" you've built on the master sheet. Set objCell = Application.Selection J = objCell.Interior.ColorIndex ' Hide columns ' If columns A - E are always going to be there, we only need to start with Column E, which ' is column number 5. For i = 6 To 168 Set objR = objWS.Cells(2, i) ' We'll always be looking at row 2 - the column will change If objR.Interior.ColorIndex < J Then objWS.Columns(i).Hidden = True End If Next Set objR = Nothing Set objCell = Nothing Set objWS = Nothing Sheets("master").Select End Sub Thanks!!!!!!! D *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If active cell value is 1, run this macro
Select Case objCell.Value
Case 1 Call x1 Case 2 Call x2 Case 3 Call x3 End Select Hints: 1) Subs can't be named "1X", can't have numeric character first. Try "X1" (or something more descriptive!) 2) Dim i As Long, Byte may work but I why limit yourself? I don't even bother with Integer anymore (unless required for a Sub or Function arg) 3) Terminology: You don't select the active cell; you select a cell and it becomes the active cell. :) "Darin Kramer" wrote: Hi There, Situation - if you select the active cell then click a box, a macro runs based on the colour in your active cell. I would like to modify the VBA slightly (see below) to say if the value of the active cell is 1, then run macro 1x (call "1X"), if cell value is 2, then call Macro 2x, etc. This is what my existing Macro looks like: Public Sub Colour_views() Call unhide_cols Dim objWS As Excel.Worksheet Dim objCell As Excel.Range, objR As Excel.Range Dim i As Byte Dim J As Long Set objWS = Sheets("Master") ' Don't forget that wherever you're looking at, the selected cell needs to have a background ' color that matches the various "views" you've built on the master sheet. Set objCell = Application.Selection J = objCell.Interior.ColorIndex ' Hide columns ' If columns A - E are always going to be there, we only need to start with Column E, which ' is column number 5. For i = 6 To 168 Set objR = objWS.Cells(2, i) ' We'll always be looking at row 2 - the column will change If objR.Interior.ColorIndex < J Then objWS.Columns(i).Hidden = True End If Next Set objR = Nothing Set objCell = Nothing Set objWS = Nothing Sheets("master").Select End Sub Thanks!!!!!!! D *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro-active cell | Excel Discussion (Misc queries) | |||
Macro to paste in the active cell the contents of a cell from another file?? | Excel Programming | |||
Macro to set Active Cell | Excel Programming | |||
Run macro in active cell | Excel Programming | |||
Run a macro in the active cell. | Excel Programming |