Re: Worksheet Selection

Tech-Archive recommends: Fix windows errors by optimizing your registry

or a modified dialog

Sub BrowseSheets()
Dim i As Integer
Dim TopPos As Integer
Dim iBooks As Integer
Dim PrintDlg As Dialog***
Dim Current*** As Work***
Dim cb As OptionButton
Application.ScreenUpdating = False


If ActiveWorkbook.ProtectStructur­e Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If


Set Current*** = Active***
Set PrintDlg = ActiveWorkbook.DialogSheets.Ad­d


iBooks = 0


TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Coun­t
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
iBooks = iBooks + 1
PrintDlg.OptionButtons.Add 78, TopPos, 150, 16.5
PrintDlg.OptionButtons(iBooks)­.Text = _
ActiveWorkbook.Worksheets(iBoo­ks).Name
TopPos = TopPos + 13
Next i


PrintDlg.Buttons.Left = 240


Current***.Activate


With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos - 34)
.Width = 230
.Caption = "Select workbooks to process"
End With


PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront


Application.ScreenUpdating = True
If PrintDlg.Show Then
For Each cb In PrintDlg.OptionButtons
If cb.Value = xlOn Then
'ActiveWorkbook.Worksheets(cb.­Caption).Select
MsgBox "Worksheet " & Worksheets(cb.Caption).Name & "
selected"
Exit For
End If
Next cb
Else
MsgBox "Nothing selected"
End If


Application.DisplayAlerts = False
PrintDlg.Delete


End Sub



--
HTH

Bob Phillips

"zackb" <firefytr@xxxxxxxxxxxxxx> wrote in message
news:eQk%23l2PUFHA.1796@xxxxxxxxxxxxxxxxxxxxxxx
> Hi Gaz,
>
> Sounds like you are wanting to use a UserForm. I'll assume this is
correct
> and you have these things installed on a UserForm:
>
> Label, saying, "Choose Worksheets" (Label1)
> ComboBox, will contain *** names (ComboBox1)
> CommandButton, saying, "OK" (CommandButton1)
> CommandButton, saying, "Close" (CommandButton2)
>
> Enter this code into your Userform ...
>
>
> Option Explicit
> Private Sub UserForm_Initialize()
> Dim tmpWs As Work***, i#, n#, ws As Work***
> Application.ScreenUpdating = False
> Application.DisplayAlerts = False
> Set ws = Active***
> Set tmpWs = Worksheets.Add(before:=Sheets(1))
> For i = 2 To Worksheets.Count
> tmpWs.Range("A" & i - 1) = Worksheets(i).Name
> Next
> tmpWs.Range("A1:A" & i - 2).Sort Key1:=Range("A1"), Header:=xlGuess
> For n = 1 To i - 2
> Me.ComboBox1.AddItem tmpWs.Range("A" & n).Text
> Next
> Me.ComboBox1.ListIndex = 0
> tmpWs.Delete
> ws.Activate
> Application.DisplayAlerts = True
> Application.ScreenUpdating = True
> End Sub
> Private Sub CommandButton1_Click()
> On Error GoTo errHandle
> Worksheets(Me.ComboBox1.Value).Activate
> Unload Me
> Exit Sub
> errHandle:
> MsgBox "An error has occured!"
> Resume
> End Sub
> Private Sub CommandButton2_Click()
> Unload Me
> End Sub
>
>
> --
> Regards,
> Zack Barresse, aka firefytr
>
> "Gazza" <mallin"nospam"@tesco.net> wrote in message
> news:zIbee.14550$wu2.13262@xxxxxxxxxxxxxxxxxxxxxxx
> > Is it possible to set up a proceedure that will take a list of all the
> > worksheets in a workbook and produce a list of these alphabetically.
This
> > list then to be used in such a way as when the user selects a particular
> > *** name they are taken straight to that ***.
> >
> > Thanks
> >
> > Gaz
> >
> >
>
>


.

Quantcast