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.

--
Regards

KrazyRed

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

"KrazyRed" wrote in message
...
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.

--
Regards

KrazyRed

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!

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" wrote:

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

"KrazyRed" wrote in message
...
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.

--
Regards

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" wrote:

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

"KrazyRed" wrote in message
...
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.

--
Regards

KrazyRed

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" wrote in message
...
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" wrote:

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

"KrazyRed" wrote in message
...
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.

--
Regards

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

"Sharad Naik" wrote:

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" wrote in message
...
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" wrote:

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

"KrazyRed" wrote in message
...
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.

--
Regards

KrazyRed

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!

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




"Sharad" wrote:

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!