run-time error 91

[Guest]

I'm having trouble with a runtime error 91. I tried to place an "On Error
Resume Next " but it only freezes the user form. What am I missing?

Private Sub OpenWkbk_Click()
Dim frmOpenWkbk As Object
Workbooks.Open Sheets("mySheet").Range("myRange").Value
frmOpenWkbk.Hide
End Sub

Thanks for your help.

 

[Guest]

You declare frmOpenWkbk but you never initialize it to anything. Is it a form
in the workbook you are opening (set to show on open)??? If that is the case
then it will have to be pointed at that form...

 

[Guest]

if the name of the form is frmOpenWkbk then don't declare it.

Private Sub OpenWkbk_Click()
frmOpenWkbk.Hide
Workbooks.Open Sheets("mySheet").Range("myRange").Value
End Sub

However, I would assume openWkbk command button is in the form you want to
hide, so the easiest is

Private Sub OpenWkbk_Click()
me.Hide
Workbooks.Open Sheets("mySheet").Range("myRange").Value
End sub

the Me keyword, refers to the userform that owns the code module where the
code is located.

 

[moon]

You say 'Workbooks.Open...' but you only mention a sheet, so the actual
workbook that you want to open is the object that is missing.
Then, frmOpenWkbk is declared as an object, but a value is never assigned
and you just say:
frmOpenWkbk.Hide, so that's the second 'unidentified object'.

Dim frmOpenWkbk As Object <--- is this a form u made?

Look at this:
¯¯¯¯¯¯¯¯¯

Private Sub OpenWkbk_Click

'declare object variables
Dim wkBook As Workbook
Dim WkSht as Worksheet

'assign values
Set wkBook = Workbooks.Open "test.xls"
Set WkSht = wkBook.Sheets( "mySheet" )

'activate the sheet object
WkSht.Activate

'get the value from a range in the sheet
MsgBox WkSht.Range( "myRange" ).Value

'I guess the form already exists, so
'there's no need to declare it, but just unload
Unload frmOpenWkbk

End Sub


Maybe it's better to tell us what you exactly want, because at this stage
that's not so clear.

 

[Guest]

Thank you al for replying.

Tom your solution was what I was looking for. It works perfect, thanks.
Regards,