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

K

KrazyRed

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.
 
S

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
 
D

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...)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 
K

KrazyRed

Thanks, that seems ok. Only problem now is that The Run Macro argument keeps
coming up with a compile error. Expected Function or Variable. This is what I
have typed in.

Private Sub DropDownCourse_Change()
Select Case Worksheets("Sheet8").Range("LINK").Value

Case 1
Run course1

Case 2
Run course2

End Select

End Sub

Should there be any code after run Course1 etc. ie.expected arguments?

LINK is cell link and course1 is macro1 etc.

Sorry to be so thick.

Sharad Naik said:
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
Click to expand...
 
K

KrazyRed

Sussed out the last bit, now a another problem.

Says its a run time error 9, subscript out of range.

This occurs when I select either case 1 or case 2 in the drop down box.

thanks

Sharad Naik said:
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
Click to expand...
 
S

Sharad Naik

When it gives error subscript out of range, click on Debug. It will take you
to the line in the code, where the error is actually generated. Please paste
that line here, so that we can see if we can help.

Sharad

KrazyRed said:
Sussed out the last bit, now a another problem.

Says its a run time error 9, subscript out of range.

This occurs when I select either case 1 or case 2 in the drop down box.

thanks
Click to expand...
 
K

KrazyRed

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
 
S

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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 
K

KrazyRed

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("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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Link a text box based on drop down box with four options MS Word 365 0
Drop down list control value in vba 2
Word macro that can call cells out of Excel for a find and replace 0
VBA Coding Help for Beginner 0
Formatting Rule: Diplay Section based on Selection in another section 0
Some trouble assigning a string to a variable 22
Macro to Format Multiple Sheets based on "Master" Format 0
Emailing a sheet from a workbook with VBA 0

Top