Userform load problems

[ROSER72]

Hi,

I'm trying to create a macro from Reflection VB to read values from an excel sheet and pass them to the terminal. I'm just beginning but I've already found the first problem due to my lack of experience. The code is pasted below:

Dim Excel As Object

Sub Call_Excel()

Set Excel = CreateObject("excel.application")

Excel.Visible = True

TestIt
UserForm1.Show

Excel.Quit
Set Excel = Nothing
End Sub

Sub TestIt()
NewFN = Excel.GetOpenFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx", Title:="Please select a file")
If NewFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Excel.Workbooks.Open Filename:=NewFN
End If
End Sub

Private Sub UserForm_Initialize()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
Me.ComboBox1.AddItem sh.Name
Next
End Sub

I'm able to launch the excel app from reflection and make the workbook selection, but when the code should show the form with the combobox populated with the worksheet names I get an error in the form initialization.

Can anyone help me please!

Roser72

 

[PHV]

I get an error
Any chance you could post the error message ?
BTW, the UserForm_Initialize procedure should be in the UserForm class module.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fumei]

Yes. Where, exactly, you you have that initializ code? Please post the text of error messages when you mention them. Also, please use the TGML code tags when posting code.

faq219-2884

Gerry
My paintings and sculpture

 

[ck1999]

Try replacing
For Each sh In ActiveWorkbook.Worksheets

with For Each sh In excel.ActiveWorkbook.Worksheets

or you may have to change

Set wb = Excel.Workbooks.Open Filename:=NewFN

then use

For Each sh In wb.Worksheets

of course you should global declare wb as well

ck1999

 

[ROSER72]

Hi I tried to replace the

Private Sub UserForm_Initialize()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
Me.ComboBox1.AddItem sh.Name
Next
End Sub

with

Private Sub UserForm_Initialize()
Dim sh As Worksheet
For Each sh In Excel.ActiveWorkbook.Worksheets
Me.ComboBox1.AddItem sh.Name
Next
End Sub

But i still get an error as sh is not defined. I guess my error is that when the userform loads the reference from module is lost, and therefore the variable dim sh as worksheet is not valid anymore.

How can I fix it? I need to keep somehow the excel create object when I call the userform.

Thanks in advance,
ROSER72

 

[fumei]

Dim sh As Worksheet

means nothing. sh is not defined, which is I believe your error. You need to fully qualify it.

Dim sh As Excel.Worksheet

BTW: again, please use the TGML code tags.

faq219-2884

Gerry
My paintings and sculpture

 

[ROSER72]

I made the change to:

Private Sub UserForm_Initialize()
Dim sh As Excel.Worksheet
For Each sh In Excel.ActiveWorkbook.Worksheets
Me.ComboBox1.AddItem sh.Name
Next
End Sub

but I still get the error user defined-type type not defined.

I've made a test to copy the userform_initialize into the module and the error does not appear, but the user form does not load the names of the worksheets. Instead when I put it back as part of the code of the form the error appears again.

Thanks,

 

[combo]

Declare excel application as public (Public Excel as Object). If you use early binding, change Excel variable to other name that does not duplicate library name.

combo

 

[ROSER72]

I've made the follwing change:

Public oExcel As Object

Sub Call_Excel()

Set oExcel = CreateObject("excel.application")
' You may have to set Visible property to True
' if you want to see the application.
oExcel.Visible = True
' Use xlApp to access Microsoft Excel's
' other objects.
TestIt
UserForm1.Show

oExcel.Quit ' When you finish, use the Quit method to close
Set Excel = Nothing ' the application, then release the reference.

End Sub
Sub TestIt()
NewFN = oExcel.GetOpenFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx", Title:="Please select a file")
If NewFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
oExcel.Workbooks.Open Filename:=NewFN
End If
End Sub



Private Sub UserForm_Initialize()

Dim sh As oExcel.Worksheet
For Each sh In oExcel.ActiveWorkbook.Worksheets
Me.ComboBox1.AddItem sh.Name
Next


End Sub

I still continue to have the same problem:

user defined-type type not defined

ROSER72

 

[PHV]

Replace this:
Set Excel = Nothing
with this:
Set oExcel = Nothing

and this:
Dim sh As oExcel.Worksheet
with this:
Dim sh As Object

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ROSER72]

This made it for me. Thanks all for your help.