Combo box question...

[mrowe]

Hi,
I posted something similar yesterday,, but didn't seem to get it to work.

I've put a combo box in a user form, and want the selected item to jump to the worksheet that lives within the workbook.

Do I need to create a list of the worksheet names? (i.e London, West, East, Cardiff) or is it some kind of tricky DIM?

Thanks in advance...
Matt

[Mr Nick]

I've put a combo box in a user form, and want the selected item to jump to the worksheet that lives within the workbook.

Does the input from this box always go to the same position on the same worksheet, or does the insertion differ dependent on the input value?

[mrowe]

Correct.

Basicly I have a user form called Utilities where I want the user to be able to select a worksheet from the drop down list (among other things). I've seen it before but don't know the code. The worksheets names won't change or be moved from a different location.

The reason for it all it a small solution for staff that are affraid of excel and want a point a click interface.

Cheers
Matt

[Mr Nick]

If you are using a UserForm, then just attach the following code to the OK button on your form.

Private Sub CommandButton1_Click()
Sheets(ComboBox1.Text).Select
Unload UserForm1
End Sub

[mrowe]

How do I populate the combo box with the sheet names?

[Mr Nick]

If you set up a list of sheet names on one of your sheets e.g. Sheet1 cells A1 to A5, then on your userform, select the combobox and put =sheet1!a1:a5 in the RowSource property, you will see whatever you put in these cells in your dropdown.

[mrowe]

thanks for all your help.
Matt

[Ivan F Moala]

On 2002-05-01 00:52, mrowe wrote:
Hi,
I posted something similar yesterday,, but didn't seem to get it to work.

I've put a combo box in a user form, and want the selected item to jump to the worksheet that lives within the workbook.

Do I need to create a list of the worksheet names? (i.e London, West, East, Cardiff) or is it some kind of tricky DIM?

Thanks in advance...
Matt

Hi Matt...silly question BUT, can't the users
just click the worksheet tab to select the
sheet OR Right click on the Navigation keys
to the left of the sheet tabs and select
the sheet from the Popup Dialog Box ??

If you must have code to do this then you could try this routine;

1) It places a commandbutton in your main
excel commandbar
2) clicking this exercutes a routine that
dynamically creates a userform that
has a Combobox with the sheet names to
goto.
3 commandbuttons
GO , BACK , CANCEL

So that you Don't need to create a Userform

In the Thisworkbook object Place this code;

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Delete_Button

End Sub



Private Sub Workbook_Open()

Create_Button

End Sub

IN STD MODULE PLACE THIS CODE;

'// Discription: Creates a UserFrom on the fly

'//     Userform contains Combobox and 3 buttons

'//     ComboBox used to select via dropdown or typing,

'//     The sheet to goto.

'//     Buttons to Cancel, Goto sheet, Go back to last

'//     selected sheet.



'//     Userform is deleted after use.



'//Upon opening the file a button is created

'//that will call the creation of the form with it's

'//buttons.......Combobox had to be filled via

'//userform initialise ?? couldn't get it ??



'//Ivan F Moala 03 05 2001



'// Used to create a button to call the routine

'// So it can be used in any workbook that is open.



'//Code to go into a seperate Module;



'//This code handles the Back button, note

'//it is robust enough to handle or use for

'//any routine in which a return from is required.





Public Sub SaveLocation(ReturnToLoc As Boolean)



Static WB As Workbook

Static WS As Worksheet

Static Rg As Range



On Error GoTo NoGo

If ReturnToLoc = False Then

    Set WB = ActiveWorkbook

    Set WS = ActiveSheet

    Set Rg = Selection

Else

    WB.Activate

    WS.Activate

    Rg.Select

End If



Exit Sub

NoGo:

MsgBox "Can't DO...Not set !"

End Sub



'To save the current location, call SetSaveLoc.

Public Sub SetSaveLoc()

    SaveLocation (False)

End Sub



'To return to the saved location, call GetSaveLoc.

Public Sub GetSaveLoc()

    SaveLocation (True)

End Sub

IN ANOTHER MODULE PUT THIS CODE IN;

Option Explicit

'Routines to go into a seperate Module;

Option Base 1



'Passed back to the function from the UserForm

Public GETOPTION_RET_VAL As Variant



Function GetOption(Title)

Dim TempForm

Dim NewComboBox As MSForms.ComboBox

Dim NewCommandButton1 As MSForms.CommandButton

Dim NewCommandButton2 As MSForms.CommandButton

Dim NewCommandButton3 As MSForms.CommandButton

Dim X As Integer, TopPos As Integer

Dim MaxWidth As Long, Ams As String, Ap As String

Dim ShName()



' Hide VBE window to prevent screen flashing

Application.VBE.MainWindow.Visible = False



' Create the UserForm

Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3)

TempForm.Properties("Width") = 300



' Add the ComBoBox

TopPos = 4

MaxWidth = 0 'Stores width of widest OptionButton

Set NewComboBox = TempForm.Designer.Controls.Add("forms.combobox.1")

With NewComboBox

    .MatchEntry = fmMatchEntryFirstLetter

    .Width = 200

    .Height = 15

    .Left = 8

    .Top = TopPos

    If .Width > MaxWidth Then MaxWidth = .Width

End With



' Add the Cancel button

Set NewCommandButton1 = TempForm.Designer.Controls.Add("forms.CommandButton.1")

With NewCommandButton1

    .Caption = "Cancel"

    .Height = 18

    .Width = 44

    .Left = MaxWidth + 12

    .Top = 6

End With



' Add the GO button

Set NewCommandButton2 = TempForm.Designer.Controls.Add("forms.CommandButton.1")

With NewCommandButton2

    .Caption = "GO"

    .Height = 18

    .Width = 44

    .Left = MaxWidth + 12

    .Top = 28

End With



' Add the Back button

Set NewCommandButton3 = TempForm.Designer.Controls.Add("forms.CommandButton.1")

With NewCommandButton3

    .Caption = "< Back"

    .Height = 18

    .Width = 44

    .Left = MaxWidth + 12

    .Top = 50

End With



Ap = Chr(34): Ams = Chr(38)

' Add event-hander subs for the CommandButtons

With TempForm.CodeModule

X = .CountOfLines

.insertlines X + 0, "Option Base 1"



.insertlines X + 1, "Sub CommandButton1_Click()"

.insertlines X + 2, " GETOPTION_RET_VAL=False"

.insertlines X + 3, " Unload Me"

.insertlines X + 4, "End Sub"



.insertlines X + 5, "Sub CommandButton2_Click()"

.insertlines X + 6, " SetSaveloc"

.insertlines X + 7, " On Error Resume Next"

.insertlines X + 8, " Sheets(ComboBox1.Text).Activate"

.insertlines X + 9, " If Err.Number <> 0 Then MsgBox " & _

Ap & "Sheet " & Ap & Ams & " ComboBox1.Text " & Ams & Ap & " doesn't exists!"

.insertlines X + 10, "End Sub"



.insertlines X + 11, "Private Sub UserForm_Initialize()"

.insertlines X + 12, "Dim ShName(),X as Integer"

.insertlines X + 13, "ReDim ShName(Sheets.Count)"

.insertlines X + 14, "For X = 1 To Sheets.Count"

.insertlines X + 15, " ShName(X) = Sheets(X).Name"

.insertlines X + 16, "Next"

.insertlines X + 17, "ComboBox1.List() = ShName()"

.insertlines X + 18, "SetSaveLoc"

.insertlines X + 19, "End Sub"



.insertlines X + 20, "Sub CommandButton3_Click()"

.insertlines X + 21, "GetSaveLoc"

.insertlines X + 22, "End Sub"



End With



' Adjust the form

With TempForm

    .Properties("Caption") = Title

    .Properties("Width") = NewCommandButton1.Left + NewCommandButton1.Width + 10

    If .Properties("Width") < 160 Then

        .Properties("Width") = 160

        NewCommandButton1.Left = 106

        NewCommandButton2.Left = 106

    End If

    .Properties("Height") = 24 * 4 'no buttons + 1

End With



' Show the form

VBA.UserForms.Add(TempForm.Name).Show



' Delete the form

ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=TempForm



' Pass the selected option back to the calling procedure

GetOption = GETOPTION_RET_VAL



End Function



Sub GotoSheet()

Dim UserChoice As Variant



UserChoice = GetOption("Select a Sheet")

If UserChoice = False Then End



End Sub



Sub Create_Button()

Dim TopButton As CommandBarButton

Set TopButton = Application.CommandBars(1).Controls.Add(Type:=msoControlButton, _

Before:=10)

With TopButton

.Style = msoButtonCaption

.Caption = "GoTo Sheet"

.OnAction = "GotoSheet"

End With

End Sub



Sub Delete_Button()

Application.CommandBars(1).Controls("GoTo Sheet").Delete

End Sub