RUN a macro based on a value selected in a drop down box in a shee

[Guest]

Well and truly stumped.

I have a named range which has 94 cells , each with a value that needs to be
selected in drop down box.

I have linked the box to the range so it is populated.

Now the problem. 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 94
entries 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
when a value is selected in the box, but it doesn't want to work! If <cell
range>.value = "1" Then (perform macro 1) then loop through each based on
the next selection.

Any help would be much appreciated.

 

[Sharad Naik]

Where you are writing that code?
You should wirite the code in the DropDown_Change Procedure.

Suppose the "LinkedCell" for the dropdown is 'E8' and the DropDown name is
DorpDown1.
Then you should wirite the code in the " Private Sub DropDown1_Change"
Procedure (In disign
mode simply double click on the drop down and it will take to this
procedure.
Code can be :-
Select Case Worksheets("Sheet Name").Range("E8").Value
Case 1
Run Macro1
Case 2
Run Macro2
..and so on
End Select

Sharad

 

[Darin Kramer]

I looked at the site, and have found thefunction that I want... However
how do I get VBA to run this function.... - I cant seem to house it
inside a Sub Macro..

(Apologies for the dumb questionaire, asi I try to teach myself more
VBA...)

 

[Guest]

Private Sub DropDownCourse_Change()
Select Case Worksheets("Courses Completed").Range("Cell_Link").Value

Case 1
Run "course1"

Case 2
Run "course2"

End Select

End Sub

 

[Sharad]

Well, so it appears that the error is generated because something is nor
properly refered in your code in macros Sub course1() and Sub
course2().

When the error appears, if you click on debug it should take you to the
exact line which is generating the error, the line even will be
highlighted. Post this line.

Sharad

 

[Guest]

Okay, 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