"Excel Problem" Need to Select Named sheet from name in a list

[peterlsw]

I have created a macro to add a new sheet and re name it I also write
the name to a list on a seperate sheet, I am trying to activate a sheet
from a selection box with the names in but am having problems can
anyone help thanks

 

[Frank Kabel]

Hi
if you have this listbox/dropdown (e.g. create via Data - Validation -
List or a linked combobox) with your sheetnames in B1 add the following
code to your
worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.count > 1 Then Exit Sub
If Intersect(Target, Me.Range("B1")) Is Nothing Then Exit Sub
With Target
If .Value <> "" Then
Worksheets(.Value).Activate
End If
End With
End Sub

 

[Dave Hawley]

Hi peter

Would need to know hat sort of List box you have? Is it on a UserForm
(ActiveX), or on a Worksheet from the Forms toolbar or the Control
Toolbox (ActiveX).

You can add a sheet and name it at the same time

Sheets.Add().Name="My Sheet"

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[peterlsw]

could you please explain how i link the code to my selection list bo
e.g comment code regards pete

 

[Frank Kabel]

Hi
if you have used a listbox created with data vliadation put my code in
the worksheet module of your worksheet. see
http://www.cpearson.com/excel/events.htm
for more details about event procedures

 

[peterlsw]

I have a button that creates a new sheet, asks for a name and rename
the sheet also adds the name to a cell in a column in a sheet. Thi
column is the control source for the combo box. when you make
selection from the combo box I want it to take the name selected an
put it into the sheet value and activate that sheet

 

[Dave Peterson]

There are some questions that need to be answered.

What/where is this object?
Is it on a worksheet or is it on a userform?

Is it a listbox or combobox/dropdown or is it from data|validation.

If it's on a worksheet, did you create it using the controls from the Control
toolbox toolbar or from the Forms Toolbar.

The code would be different in each case.

I used a combobox from the controltoolbox toolbar on a worksheet and I could
doubleclick on that control and paste this code in the code window. I could use
the same code behind the userform.

Option Explicit
Dim blkProc As Boolean
Private Sub ComboBox1_Change()
Dim testWks As Worksheet

If blkProc = True Then Exit Sub

If Me.ComboBox1.Value = "" Then
'do nothing
Else
Set testWks = Nothing
On Error Resume Next
Set testWks = ThisWorkbook.Worksheets(Me.ComboBox1.Value)
On Error GoTo 0
If testWks Is Nothing Then
MsgBox "Please fix rowsource"
Else
testWks.Activate
Application.Goto testWks.Range("a1"), Scroll:=True
End If
blkProc = True
Me.ComboBox1.ListIndex = -1
blkProc = False
End If
End Sub

====
If you used a dropdown from the Forms toolbar, you could assign this macro to
the dropdown:

Option Explicit
Sub DDChange()

Dim myDD As DropDown
Dim testWks As Worksheet

Set myDD = ActiveSheet.DropDowns(Application.Caller)

If myDD.ListIndex < 1 Then
'do nothing
Else
Set testWks = Nothing
On Error Resume Next
Set testWks = ThisWorkbook.Worksheets(myDD.List(myDD.ListIndex))
On Error GoTo 0
If testWks Is Nothing Then
MsgBox "Please fix rowsource"
Else
testWks.Activate
Application.Goto testWks.Range("a1"), Scroll:=True
End If
End If
myDD.ListIndex = 0

End Sub