Select worksheet with Application Input box???

[neeraja]

Hi,

Could someone let me know how to Select the Name of the
Worksheet with Application Input box.

Right now in my Macro I am providing the user with an
option to select the Excel file. The code is as follows:

Sub OpenOneFile()

ExcelFile1 = Application.GetOpenFilename("Excel-
files,*.xls", _
1, "Select Input File To Open", , False)
If TypeName(ExcelFile1) = "Boolean" Then Exit Sub

Debug.Print "Selected file: " & ExcelFile1
Workbooks.Open ExcelFile1
End Sub


After selecting the Excel File, the user must be Provided
with an option to select the Excel Sheet which has the
input Data for the Macro. How do I do this????

I appreciate any help.
Thanks,
Neeraja.
..

 

[Dave Peterson]

You could ask the user to type in the name of the worksheet, but another option
would be to just let them click on a cell on the worksheet that they want.

Option Explicit
Sub OpenOneFile()

Dim ExcelFileName1 As Variant
Dim ExcelWorkbook1 As Workbook
Dim myRng As Range

ExcelFileName1 = Application.GetOpenFilename("Excel files,*.xls", _
1, "Select Input File To Open", , False)

If TypeName(ExcelFileName1) = "Boolean" Then Exit Sub

Debug.Print "Selected file: " & ExcelFileName1
Set ExcelWorkbook1 = Workbooks.Open(ExcelFileName1)

Set myRng = Nothing
'On Error Resume Next
Set myRng = Application.InputBox _
(prompt:="Please click on a cell on the worksheet to process", _
Title:="Select Range", Type:=8)
On Error GoTo 0

If myRng Is Nothing Then
'user hit cancel
ExcelWorkbook1.Close savechanges:=False 'close the workbook???
Else
'the parent of the range is the worksheet.
Call YourMacroHere(myRng.Parent)
End If

End Sub
Sub YourMacroHere(wks As Worksheet)
MsgBox wks.Name & wks.Parent.FullName
End Sub

But if you want to have the user type, then this might get you started:

Sub getwksName()
Dim wks As Worksheet
Dim Str As String

Set wks = Nothing
Do
Str = InputBox(Prompt:="Type a worksheet name")
If Str = "" Then
MsgBox "quitter"
Exit Do
Else
On Error Resume Next
Set wks = ActiveWorkbook.Worksheets(Str)
On Error GoTo 0
If wks Is Nothing Then
'typo, keep asking
Else
Exit Do
End If
End If
Loop

If wks Is Nothing Then
'user cancelled--what happens here
Else
MsgBox "your worksheet's name is: " & wks.Name
End If
End Sub