How do i select worksheet using an inputbox in vba

Question

I am trying to select a worksheet every time when i open up a workbook using an inputbox in VBA. here is my code for opening a workbook but after i open up my workbook, how do i select a worksheet inside that workbook?

     Sub button7_click()
         dim wb as string
         dim ss as string

           wb = Application.GetOpenFilename
               if wb <> "False" Then Workbooks.Open wb

      End sub

workbooks(wb).worksheets(yourWS) – Horaciux Jul 15 '14 at 18:56 — Horaciux, Jul 15 '14 at 18:56

whistler · Answer 1 · 2014-07-16T14:13:17.203

1

Assuming "Sheet1" is the name of the sheet that you want to select...

Workbooks(wb).Sheets("Sheet1").Select

EDIT: And you can use something like this to get a variable sheet name from an InputBox. In its simplest form...

Dim Result As String

Result = InputBox("Provide a sheet name.")
Workbooks(wb).Sheets(Result).Select

...but I would add some error handling into this also to prevent errors from blanks, misspelled or invalid sheet names.

edited Jul 16 '14 at 14:13

answered Jul 15 '14 at 19:16

whistler

311
3
11

sorry about confusion, i want for the user to prompt a box to choose worksheet, what if they have a different sheet name like aa instead sheet1 – user3793997 Jul 16 '14 at 00:56
then use inputbox, or even msgbox. Following the microsoft help link (F1) of these should suffice for explanations. – Patrick Lepelletier Jul 16 '14 at 11:53
Updated the answer with InputBox approach. – whistler Jul 16 '14 at 15:18

score 0 · Answer 2 · edited May 23 '17 at 11:57

Let's say you have a "normal", blank Excel workbook with sheets "Sheet1", "Sheet2" and "Sheet3". Now, when the workbook opens, let's assume you want to activate (not select, as that's different) the sheet called "Sheet2".

In your workbook's ThisWorkbook module, add this code:

Private Sub Workbook_Open()

  ActiveWorkbook.Sheets("Sheet2").Activate

End Sub

Make sure this code is pasted inside of the ThisWorkbook object and not in a Module, Form, or Sheet object.

Save and exit the workbook. When you re-open it, "Sheet2" will be the active sheet.

score 0 · Answer 3 · answered Mar 04 '21 at 11:48

Here is the final code if anyone wants it. Multiple selections are not quite possible , as the copied worksheet only copies across and increments the largest value of the range selected rather than all the cells selected individually ....

Sub CopyAndIncrement()
Dim ws As Worksheet
Dim Count As Integer
Dim Rng As Range
Dim myValue As Integer
Dim wsName As String


wsName = InputBox("Provide the EXACT sheet name you want to copy.")
'error handling loop for Worksheet name
For p = 1 To Worksheets.Count
    If Worksheets(p).Name = wsName Then
        exists = True
    End If
Next p
   
  If Not exists Then
    While exists = False
     wsName = InputBox("Sheet not found re-enter name.")
        For p = 1 To Worksheets.Count
            If Worksheets(p).Name = wsName Then
            exists = True
            End If
        Next p
    Wend
  End If

Set Rng = Application.InputBox( _
  Title:="Increment your worksheet", _
  Prompt:="Select a cell(s) you want to increment", _
  Type:=8)
On Error GoTo 0
If Rng Is Nothing Then Exit Sub 'Test to ensure User Did not cancel

'Set Rng = Rng.Cells(1, 1) 'Set Variable to first cell in user's input (ensuring only 
'1 cell) >> commenting this can give multiple selections
 
 myValue = InputBox("How many time do you want it to increment? Give me the number ")
  
    Do While Count < myValue
      
      For Each ws In Worksheets   ' this loop selects the last sheet in the workbook
          LastWs = ws.Name
          i = ws.Range(Rng.Address).Value
          If i > j Then
              j = i
          End If
      Next
      
    Sheets(wsName).Select
    Sheets(wsName).Copy After:=Sheets(LastWs)
    ActiveSheet.Range(Rng.Address).Value = j + 1
    Count = Count + 1
    Loop
    End Sub

How do i select worksheet using an inputbox in vba

3 Answers3