Selecting a sheet using a dropdown box, but excluding the sheet that hosts the dropdown box

[jap7675]

Hello Everyone,

I have this code;

Private Sub Workbook_Activate()

Dim oSheet As Excel.Worksheet
Dim oCmbBox As MSForms.ComboBox

Set oCmbBox = ActiveWorkbook.Sheets("cmbSheet").cmbSheet

oCmbBox.Clear

For Each oSheet In ActiveWorkbook.Sheets

oCmbBox.AddItem oSheet.Name

Next oSheet

End Sub

That creates a drop down box of all the sheets in the workbook - it does this fine. However, I would like to exclude the sheet that the drop down box is located on. How can i do this?

Thanks a bunch!

 

[GTO]

Greetings,

Try:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>  <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Activate()<br><SPAN style="color:#00007F">Dim</SPAN> oSheet <SPAN style="color:#00007F">As</SPAN> Excel.Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> oCmbBox <SPAN style="color:#00007F">As</SPAN> MSForms.ComboBox<br>  <br>  <SPAN style="color:#00007F">Set</SPAN> oCmbBox = Sheet1.ComboBox1 <SPAN style="color:#007F00">'<--- I used the CodeName instead</SPAN><br>  <br>  oCmbBox.Clear<br>  <br>  <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> oSheet <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Worksheets<br>      <br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> oSheet.Name = oCmbBox.Parent.Name <SPAN style="color:#00007F">Then</SPAN><br>      oCmbBox.AddItem oSheet.Name<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>  <br>  <SPAN style="color:#00007F">Next</SPAN> oSheet<br>  <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Mark

 

[jap7675]

This is perfect GTO, thanks so much!

 

[jap7675]

Is there a way of using the sheet selected in the 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!

Thanks

 

[GTO]

Hi again and of course you are very welcome.

If I understand your last question, you should be able to use the combo box's change event to set a sheet and then do something, like maybe select that sheet...

In ThisWorkbook:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>  <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Activate()<br><SPAN style="color:#00007F">Dim</SPAN> oSheet <SPAN style="color:#00007F">As</SPAN> Excel.Worksheet<br>  <br>  <SPAN style="color:#00007F">With</SPAN> Sheet1.ComboBox1<br>    <br>    .Clear<br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> oSheet <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Worksheets<br>      <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> oSheet.Name = .Parent.Name <SPAN style="color:#00007F">Then</SPAN><br>        .AddItem oSheet.Name<br>      <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> oSheet<br>  <br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>  <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

In Sheet1's module:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>  <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> ComboBox1_Change()<br><SPAN style="color:#00007F">Dim</SPAN> wks <SPAN style="color:#00007F">As</SPAN> Worksheet<br>  <br>  <SPAN style="color:#007F00">' In case the combobox has just been cleared (and the change event is fired), we ignore</SPAN><br>  <SPAN style="color:#007F00">' the possible error for just now...</SPAN><br>  <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>  <SPAN style="color:#00007F">Set</SPAN> wks = ThisWorkbook.Worksheets(Me.ComboBox1.Value)<br>  <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>  <br>  <SPAN style="color:#007F00">' ...and then test.  If a valid sheetname being picked in the combobox is what</SPAN><br>  <SPAN style="color:#007F00">' fired the change event, then the reference was set and we select the sheet.</SPAN><br>  <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> wks <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>    wks.Select<br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>  <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

I hope that this is along the lines of what you were asking,

Mark

 

[jap7675]

Thanks so much, however I dont quite understand what effect this has.

If i can expand on my problem further...
I was hoping to select a sheet with the drop down, then this selection could be used in a lot of formula (an example of which is below)

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

Wherever ActiveSheet appears, it is intended that this is the sheet selected in the dropdown box.

Does this alter the solution at all?

Thanks again and again!

 

[GTO]

Hello,

Rather than select the sheet, if you are building formulas, maybe just use the value of the checkbox to assist in building the string for the formula. Not checked (the formula), but maybe something like:

ThisWorkbook:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>  <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Activate()<br><SPAN style="color:#00007F">Dim</SPAN> oSheet <SPAN style="color:#00007F">As</SPAN> Excel.Worksheet<br>  <br>  <SPAN style="color:#00007F">With</SPAN> Sheet1.ComboBox1<br>    <br>    .Clear<br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> oSheet <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Worksheets<br>      <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> oSheet.Name = .Parent.Name <SPAN style="color:#00007F">Then</SPAN><br>        .AddItem oSheet.Name<br>      <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> oSheet<br>  <br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>  <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

In the worksheet's module that the combobox is on and I presume where you want the formula to end up:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>  <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> ComboBox1_Change()<br>  strSheetName = Me.ComboBox1.Value<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

In a Standard Module:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>  <br><SPAN style="color:#00007F">Public</SPAN> strSheetName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>  <br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Example()<br><SPAN style="color:#00007F">Dim</SPAN> wks <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> strFormulaString <SPAN style="color:#00007F">As</SPAN> String<br>  <br>  <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>  <SPAN style="color:#00007F">Set</SPAN> wks = ThisWorkbook.Worksheets(Sheet1.ComboBox1.Value)<br>  <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>  <br>  <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> wks <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>    strFormulaString = "=IFERROR(INDEX('" & strSheetName & "'!$G$3:$G$300,SMALL(IF('" & strSheetName & "'!$F$2:$F$300=1,ROW('" & strSheetName & "'!$F$2:$F$300)-2),ROWS($G$2:G2))),"""""""")"<br>    Sheet1.Range("E22").FormulaArray = strFormulaString<br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>  <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Hope that helps,

Mark