Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a MACRO automatically by selecting value from combo box
This is the challenge
I have set up a macro that basically filters cells from another sheet into a new sheet. There is a new macro for each of the options in the range. I want to be able to run the correct macro automatically based on the selection in the drop down box. I've experimented with a If and Then statement based on a cell link number I've simplified the sheet and started the code from scratch and set up 3 simple macros that jump to another sheet, filter information from it, copy the filtered data to sheet2 and added some formatting. I've called the macros, macofficer, macsupervisor and macmanager. The combo box is on sheet1. The code is on the combo box 1 change control The data to be filtered is on sheet3 and is filtered to sheet 2. The combo box is populated by a range called test1, which is 4 cells of data on sheet3, the link cell is H3 on sheet1, where the combo box is sited. The code of the combo box is behind sheet1, in the change procedure, as follows: Private Sub ComboBox1_Change() Select Case Worksheets("Sheet1").Range("H3").Value Case 1 Run (macofficer) Case 2 Run (macsupervisor) Case 3 Run (macmanager) End Select End Sub The macros are in Module 1 as follows: Sub macofficer() ' ' macofficer Macro ' Macro recorded 09/12/2004 by Administrator ' ' Sheets("Sheet3").Select Selection.AutoFilter Field:=3, Criteria1:="officer" Range("A4:C5").Select Selection.Copy Sheets("Sheet2").Select Range("B4").Select ActiveSheet.Paste Columns("C:C").ColumnWidth = 12.14 Range("B4:D5").Select Application.CutCopyMode = False With Selection.Font .Name = "Arial" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Selection.Font.Bold = True With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With Selection.Font.ColorIndex = 2 Range("B2:D3").Select Range("B3").Activate ActiveCell.FormulaR1C1 = "Officer" Range("B2:D3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("B2:D3").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone Range("E10").Select End Sub Sub macsupervisor() ' ' macsupervisor Macro ' Macro recorded 09/12/2004 by Administrator ' ' Sheets("Sheet3").Select Selection.AutoFilter Field:=3, Criteria1:="supervisor" Range("A3:C17").Select Selection.Copy Sheets("Sheet2").Select Range("B3").Select ActiveSheet.Paste Application.CutCopyMode = False With Selection.Font .Name = "Arial" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Selection.Font.Bold = True With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("B3:D3").Select With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With Selection.Font.ColorIndex = 2 Range("B1:D2").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With ActiveCell.FormulaR1C1 = "Supervisor" Range("D6").Select End Sub Sub macmanager() ' ' macmanager Macro ' Macro recorded 09/12/2004 by Administrator ' ' Sheets("Sheet3").Select Selection.AutoFilter Field:=3, Criteria1:="manager" Range("A2:C13").Select Selection.Copy Sheets("Sheet2").Select Range("B3").Select ActiveSheet.Paste Columns("C:C").ColumnWidth = 9.57 Range("B3:D3").Select With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With Selection.Font.ColorIndex = 2 Selection.Font.Bold = True With Selection.Font .Name = "Arial" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 2 End With With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("B1:D2").Select ActiveCell.FormulaR1C1 = "Manager" Range("D6").Select End Sub When I select the combo box to select one of the 4 options, it shows an error in VB called Compile Error, Expected function or variable. When I ok this, it jumps to this: Private Sub ComboBox1_Change() which is highlighted in yellow. When i click on run again, it jumps down to the run macro line: Run (macofficer) HIghlighting in blue the macofficer section, repeating the compile error as before. Sorry I can't be more specific, but that is it really. Thanks v much -- Regards KrazyRed |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run a MACRO automatically by selecting value from combo box
~ Case 1
Call macofficer Case 2 Call macsupervisor Case 3 Call macmanager Call isn't required, but makes the code easier to read Patrick Molloy Microsoft Excel MVP "KrazyRed" wrote: This is the challenge I have set up a macro that basically filters cells from another sheet into a new sheet. There is a new macro for each of the options in the range. I want to be able to run the correct macro automatically based on the selection in the drop down box. I've experimented with a If and Then statement based on a cell link number I've simplified the sheet and started the code from scratch and set up 3 simple macros that jump to another sheet, filter information from it, copy the filtered data to sheet2 and added some formatting. I've called the macros, macofficer, macsupervisor and macmanager. The combo box is on sheet1. The code is on the combo box 1 change control The data to be filtered is on sheet3 and is filtered to sheet 2. The combo box is populated by a range called test1, which is 4 cells of data on sheet3, the link cell is H3 on sheet1, where the combo box is sited. The code of the combo box is behind sheet1, in the change procedure, as follows: Private Sub ComboBox1_Change() Select Case Worksheets("Sheet1").Range("H3").Value Case 1 Run (macofficer) Case 2 Run (macsupervisor) Case 3 Run (macmanager) End Select End Sub The macros are in Module 1 as follows: Sub macofficer() ' ' macofficer Macro ' Macro recorded 09/12/2004 by Administrator ' ' Sheets("Sheet3").Select Selection.AutoFilter Field:=3, Criteria1:="officer" Range("A4:C5").Select Selection.Copy Sheets("Sheet2").Select Range("B4").Select ActiveSheet.Paste Columns("C:C").ColumnWidth = 12.14 Range("B4:D5").Select Application.CutCopyMode = False With Selection.Font .Name = "Arial" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Selection.Font.Bold = True With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With Selection.Font.ColorIndex = 2 Range("B2:D3").Select Range("B3").Activate ActiveCell.FormulaR1C1 = "Officer" Range("B2:D3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("B2:D3").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone Range("E10").Select End Sub Sub macsupervisor() ' ' macsupervisor Macro ' Macro recorded 09/12/2004 by Administrator ' ' Sheets("Sheet3").Select Selection.AutoFilter Field:=3, Criteria1:="supervisor" Range("A3:C17").Select Selection.Copy Sheets("Sheet2").Select Range("B3").Select ActiveSheet.Paste Application.CutCopyMode = False With Selection.Font .Name = "Arial" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Selection.Font.Bold = True With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("B3:D3").Select With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With Selection.Font.ColorIndex = 2 Range("B1:D2").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With ActiveCell.FormulaR1C1 = "Supervisor" Range("D6").Select End Sub Sub macmanager() ' ' macmanager Macro ' Macro recorded 09/12/2004 by Administrator ' ' Sheets("Sheet3").Select Selection.AutoFilter Field:=3, Criteria1:="manager" Range("A2:C13").Select Selection.Copy Sheets("Sheet2").Select Range("B3").Select ActiveSheet.Paste Columns("C:C").ColumnWidth = 9.57 Range("B3:D3").Select With Selection.Interior .ColorIndex = 11 .Pattern = xlSolid End With Selection.Font.ColorIndex = 2 Selection.Font.Bold = True With Selection.Font .Name = "Arial" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 2 End With With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("B1:D2").Select ActiveCell.FormulaR1C1 = "Manager" Range("D6").Select End Sub When I select the combo box to select one of the 4 options, it shows an error in VB called Compile Error, Expected function or variable. When I ok this, it jumps to this: Private Sub ComboBox1_Change() which is highlighted in yellow. When i click on run again, it jumps down to the run macro line: Run (macofficer) HIghlighting in blue the macofficer section, repeating the compile error as before. Sorry I can't be more specific, but that is it really. Thanks v much -- Regards KrazyRed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatically selecting contiguous cells - Why? | New Users to Excel | |||
Selecting list to populate combo box | Excel Discussion (Misc queries) | |||
Automatically selecting the next empty row | Excel Discussion (Misc queries) | |||
How to stop Excel from automatically selecting cells? | Excel Discussion (Misc queries) | |||
selecting a file automatically | Excel Programming |