Trouble with sharing variables between procedures 1

[Storyteller]

Hello All,
I've been working on the following and cannot figure out how to get the wkbook variable from the UserForm_Initialize() to be available in the cmdImport_Click() procedure.

This is the code I am working with:

Public Sub cmdImport_Click()
Dim i As Integer
Dim wkbook As Workbook
On Error Resume Next

For i = 0 To lstWorkSheets.ListCount - 1
If lstWorkSheets.Selected(i) = True Then
ActiveWorkbook.Worksheets(i + 1).Copy _
after:=Workbooks("Calculations").Sheets("StartingSheet")
End If
Next i

Workbooks(wkbook).Close 'This does not close
Unload Me

End Sub

Public Sub UserForm_Initialize()

Dim FileToOpen As String
Dim FileToUse As String
Dim wkbook As Workbook
Dim wksheet As Worksheet

FileToOpen = Application.GetOpenFilename("Excel (*.xls), *.xls")
Workbooks.Open Filename:=FileToOpen
FileToUse = Right(FileToOpen, Len(FileToOpen) _
- (InStrRev(FileToOpen, "\", -1)))
Set wkbook = Workbooks(FileToUse)

With Me.lstWorkSheets
For Each wksheet In wkbook.Worksheets
.AddItem (wksheet.Name)
Next
End With

End Sub

Everything works great except for the wkbook file does not close.

Any help would be greatly appreciated.

Regards,
Michael

 

[PHV]

Remove the Dim wkbook As Workbook in the procedures body and add this line in the Declarations section:
Public wkbook As Workbook

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Storyteller]

Hello PHV,
Thanks for your quick response!!!

I did as you suggested and initially it did not work. However, I then noticed that Workbooks(wkbook).Close should now be wkbook.Close

Everything works great now.

Have a star!!

Regards,
Michael

 

[Storyteller]

Hello PHV,

Now things are getting a little weird. . .

The code now works successfully on Win98 and Office 2000, however I copied the file to a WinXP Pro, Office 2000 computer and the code does not work properly.

Well, the opening of the file and listing the worksheets in the listbox works fine and so does the closing of the workbook. But what does not work is copying the worksheets from one workbook to another.

The problem is "Could not get the Selected property. Invalid arguement. . ."

I debug.print the lstWorkSheets.Selected(i) and it is showing True and False correctly when I have selected worksheets in the listbox. The problem is that the code is not copying the worksheet when True.

Any comments or suggestions? I will be testing the code out on additional computers to see if this is an isolated problem or something else.

Thanks for your advice.

Regards,
Michael