Run a MACRO automatically by selecting value from combo box

[Guest]

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("B45").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("B23").Select
Range("B3").Activate
ActiveCell.FormulaR1C1 = "Officer"
Range("B23").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("B23").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("B33").Select
With Selection.Interior
.ColorIndex = 11
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
Range("B12").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("B33").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("B12").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

 

[Guest]

~> 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