Using a dropdown to select a sheet, then using that sheet in VBA code

[jap7675]

Is there a way of using a sheet selected in a dropdown box as the sheet in a vba formula?
I would like the code to change which sheet it uses depending on the dropdown sheet selected!

This is the code used to programme the dropdown;
Option Explicit

Private Sub Workbook_ActivateSheet()
Dim oSheet As Excel.Worksheet
Dim oCmbBox As MSForms.ComboBox

Set oCmbBox = Sheets("summary").cmbSheet

oCmbBox.Clear

For Each oSheet In ThisWorkbook.Worksheets

If Not oSheet.Name = oCmbBox.Parent.Name Then
oCmbBox.AddItem oSheet.Name
End If

Next oSheet

End Sub


And this is an example of the formula i would like to change sheet with the dropdown box. I have tried to use "ActiveSheet" after assuming that the dropdown box activates the sheet selected.

Private Sub CheckBox1_Click()

SelectSheet = Sheets("Summary").Range("C3").Select
Range("A20") = SelectSheet

'High Box True and Moderate Box False'
If CheckBox1.Value = True Then
CheckBox2.Value = False

With Sheets("Summary").Range("E22")
.FormulaArray = "=IFERROR(INDEX(ActiveSheet(G$3:G$300), SMALL(IF(AcitveSheet($F$2:$F$300)=1, ROW(ActiveSheet($F$2:$F$300))-2),ROWS(G$2:G2))),"""")"
.AutoFill Destination:=Range("E22:E244"), Type:=xlFillDefault
End With
With Sheets("Summary").Range("F22")
.FormulaArray = "=IFERROR(INDEX(ActiveSheet(H$3:H$300), SMALL(IF(ActiveSheet($F$2:$F$300)=1, ROW(ActiveSheet($F$2:$F$300))-2),ROWS(G$2:G2))),"""")"
.AutoFill Destination:=Range("F22:F244"), Type:=xlFillDefault
End With
With Sheets("Summary").Range("D22")
.FormulaArray = "=IFERROR(INDEX(ActiveSheet(U$3:U$300), SMALL(IF(ActiveSheet($F$2:$F$300)=1, ROW(ActiveSheet($F$2:$F$300))-2),ROWS(G$2:G2))),"""")"
.AutoFill Destination:=Range("D22:D244"), Type:=xlFillDefault
End With
With Sheets("Summary").Range("C22")
.FormulaArray = "=IFERROR(INDEX(ActiveSheet(V$3:V$300), SMALL(IF(ActiveSheet($F$2:$F$300)=1, ROW(ActiveSheet($F$2:$F$300))-2),ROWS(G$2:G2))),"""")"
.AutoFill Destination:=Range("C22:C244"), Type:=xlFillDefault
End With

Sheets("Summary").Range("C20").Value = "High Alarm Locations"
Sheets("Summary").Range("C20:F20").Interior.ColorIndex = 3
With Range("C20").Font
.Bold = True
.Underline = True
.Size = 14
End With

Sheets("Summary").Range("B1", Cells(Range("T2").Value, "B")).Interior.ColorIndex = 37
Sheets("Summary").Range("G1", Cells(Range("T2").Value, "G")).Interior.ColorIndex = 37
Sheets("Summary").Range("B1:G2").Interior.ColorIndex = 37
Sheets("Summary").Range(Cells(Range("T2").Value, "B"), Cells(Range("U2").Value, "G")).Interior.ColorIndex = 37
Sheets("Summary").Range("C22", Cells(Range("S2").Value, "F")).Interior.ColorIndex = 2

End If

'High Box False and Moderate Box False'
If CheckBox1.Value = False Then
If CheckBox2.Value = False Then
Sheets("Summary").Range("E22:E500").Value = " "
Sheets("Summary").Range("F22:F500").Value = " "
Sheets("Summary").Range("D22:D500").Value = " "
Sheets("Summary").Range("C22:C500").Value = " "

Sheets("Summary").Range("C20").Value = none
Sheets("Summary").Range("C20:F20").Interior.ColorIndex = none
With Range("C20").Font
.Bold = True
.Underline = True
.Size = 14
End With

Sheets("Summary").Range("B22", Cells(Range("T2").Value, "B")).Interior.ColorIndex = none
Sheets("Summary").Range("G22", Cells(Range("T2").Value, "G")).Interior.ColorIndex = none
Sheets("Summary").Range(Cells(Range("T2").Value, "B"), Cells(Range("U2").Value, "G")).Interior.ColorIndex = none
Sheets("Summary").Range("C22", Cells(Range("S2").Value, "F")).Interior.ColorIndex = none

End If
End If

Worksheets("Summary").Calculate
DoEvents

End Sub

Summary is the sheet which the dropdown is located on.


Thanks so so so much if you can help!

 

[Richard U]

add this...

For Each oSheet In ThisWorkbook.Worksheets
oSheet.activate

 

[jap7675]

Thanks very much, but ot which block of code should i add this to?