Worksheet name

[Guest]

Hi,
Hope someone can help.
I created a form and has a text box in it. User key in the name of the
worksheet in the text box but I stumble upon a compile error. My code is as
below

Private Sub cmdUpdate_Click()
Dim fname, ws1 As String
Dim ws As Worksheet
fname = txtFileName

Workbooks.Open fname
Set ws = txtWorksheet
If ws = "" Then
MsgBox "Please input name of Worksheet"
End If
Call Module1.CopyPrice(ws, 4)

End Sub

 

[Norman Jones]

Hi Anthony,

Try something like:

'=============>>
Private Sub cmdUpdate_Click()
Dim fname As String, ws1 As String
Dim ws As Worksheet

fname = txtFileName.Text

Workbooks.Open fname
On Error Resume Next
Set ws = ActiveWorkbook.Sheets(txtWorksheet.Text)
If ws Is Nothing Then
MsgBox "Please input name of Worksheet"
End If
On Error GoTo 0

If Not ws Is Nothing Then
Call Module1.CopyPrice(ws, 4)
End If

End Sub
'<<=============

 

[Guest]

Hi Norman,
Thanks. I tried but when I run to the below line, I get a "type mismatch"
error.
Set ws = ActiveWorkbook.Sheets(txtWorksheet.Text)

Could there be something wrong with the declaration I made?

Thanks & Regards
Anthony

 

[Norman Jones]

Hi Anthony,

I assumed that txtWorksheet was a TextBox and,
in such case, using the suggested code, I was
unable to reproduce your error.

Could there be something wrong with the declaration I made?

Post the code which you used

 

[Guest]

Hi Norman

Below my code

Private Sub cmdBrowse_Click()

Dim fname, fn As String

fn = Application.GetOpenFilename("Excel-files,*.xls", _
1, "Select Panel Price file", , False)
txtFileName = fn 'Display to text box
Workbooks.Open fn

For Each sh In ActiveWorkbook.Sheets
Me.cbPriceWS.AddItem sh.Name
Next

Workbooks.Open fname

On Error Resume Next
Set ws = ActiveWorkbook.Sheets(txtWorksheet.Text)
If ws Is Nothing Then
MsgBox "Please input name of Worksheet"
End If
On Error GoTo 0

If Not ws Is Nothing Then
Call Module1.CopyPrice(ws, 4)
End If

End Sub

Private Sub cmdBUCA_Click()

Dim fn As String
fn = Application.GetOpenFilename("Excel-files,*.xls", _
1, "Select BUCA file", , False)
txtBUCA = fn 'Display to textbox

End Sub

Private Sub cmdCancel_Click()

ActiveWorkbook.Close False

End Sub

Private Sub cbPriceWS_Change()

End Sub

Private Sub txtWorksheet_Change()

End Sub

Private Sub txtFilename_Change()

End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()

TemplateFile = ActiveWorkbook.FullName
i = 0
While InStr(i + 1, TemplateFile, Application.PathSeparator) > 0
i = InStr(i + 1, TemplateFile, Application.PathSeparator)
Wend
txtTemplate = Right(TemplateFile, Len(TemplateFile) - i) 'wb1 = filename
of template

cbMonth.List = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", _
"Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
cbMonth.ListIndex = 0
'cbMonth.Locked = True
cbRegion.List = Array("Europe", "Nafta", "AP/China", "LATAM")
cbRegion.ListIndex = 0

txtFileName = ""
txtWorksheet = ""

End Sub