CODEWITHSUNDEEP
excelvbacombobox
Abigal
Abigal

Reputation: 143

ComboBox selection of a workseet, hide and unhide worksheets

I created this VBA that linked to a comboBox which is on the main worksheet called "ActiveX". This comboBox populates the name of each existing worksheet automatically even if worksheet names are changed, worksheets are added or deleted. When a dropdown item of the combobox representing a worksheet is selected, the user is taken to the selected worksheet.

What I am working on, is trying to add to this:

  1. Once on the selected worksheet, I want to add a button that takes me back to the main worksheet called “ActiveX”

  2. Hide all worksheets apart from the selected worksheet and the worksheet called “activeX” (which is the main menu worksheet)

Since additional worksheets can occasionally be added or removed by the user, is it possible to automatically insert a button to the selected worksheet only. Instead of having to add a button per worksheet?

I am not proefficient at creating VBA code so I try to copy, amend and adapt from sites like this one.

`Private Sub cbSheet_Change()
      If cbSheet.Value <> "Select Item" Then
      Worksheets(cbSheet.Value).Select
      End If
      cbSheet.Value = "Select Item"
End Sub

Private Sub Worksheet_Activate()
     Dim Sh As Worksheet
     Me.cbSheet.Clear
     For Each Sh In ThisWorkbook.Worksheets
     Me.cbSheet.AddItem Sh.Name
 Next Sh

End Sub

Upvotes: 2

Views: 990

Answers (1)

Davesexcel
Davesexcel

Reputation: 6984

Hide and Unhide sheets

Sub Hide_SH()
    Dim sh As Worksheet
    For Each sh In Sheets
        If sh.Name <> "ActiveX" Then sh.Visible = False
    Next sh
End Sub
Sub UnHide_SH()
    Dim sh As Worksheet
    For Each sh In Sheets
        sh.Visible = True
    Next sh
End Sub

You can add a hyperlink to the selected sheet.

Private Sub cbSheet_Change()
    Dim ws As Worksheet
    If cbSheet.Value <> "Select Item" Then
        Set ws = Sheets(Me.cbSheet.Value)
        With ws
            .Visible = True
            .Select
            .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", SubAddress:= _
                            "ActiveX!A1", TextToDisplay:="ActiveX!A1"
        End With
    End If
    cbSheet.Value = "Select Item"
End Sub

Upvotes: 0

Related Questions