Problem with Userform the second time

[Kevin]

I have a macro in my personal.xls attached to a toolbar that opens a userform
and displays various files in a listbox along with a date in a textbox. I can
select a file and date and then run a command (code below) button to open
that file. It works the first time but when I open the userform the next time
and select a file I get the following error
"Could not complete the operation due to error 800a01a8". I close the
userform and it works the next time.

Basically it does not work after each time I press the command button - i
get error above, close the userform, open it again, it them works next time
and we start the loop again

What I can see is when it does not work the command button looks like it has
focus but also the listbox has the file highlighted I opened the previous
time. When it does works the command button has no focus and nothing is
highlighted in the listbox and the cursor is flashing in the text box

any ideas what problem is and how to fix



Private Sub CommandButton1_Click()

Dim wbOpenFile As Workbook
ActiveSheet.Calculate

Set wbOpenFile = ActiveWorkbook

Dim PLpath As String
PLpath = Range("PLpath")
Workbooks.Open Filename:=PLpath

With UserForm1
.Hide
End With
wbOpenFile.Close savechanges = False

 

[Joel]

It looks like you are closing the workbook with the macro in it. You set Set
wbOpenFile = ActiveWorkbook and then close this file.

I think the problem is the second time you run the macro a differnt
workbook is the active workbook. I added a variable below to the workbook
that yuu opened. Make sure you are closing the correct workbook and that the
correct workbook is the active workbook. It is always dangerous to use
activeworkbook when more than one workbook is opened.



Private Sub CommandButton1_Click()

Dim wbOpenFile As Workbook
ActiveSheet.Calculate

Set wbOpenFile = ActiveWorkbook

Dim PLpath As String
PLpath = Range("PLpath")
set PLbk = Workbooks.Open(Filename:=PLpath)

With UserForm1
.Hide
End With
wbOpenFile.Close savechanges = False

 

[Kevin]

Hi - didnt seem to fix the problem - also I removed the code to close the
file to see if that would help but it didnt. It seems to be the loading of
the userform. the userform is part of my personal.xls. Perhaps it should be
luanched from the workbook where i store all the filenames?

 

[Joel]

When you open a workbook the focus switches to the opened workbook try this

Set wbOpenFile = ActiveWorkbook

Dim PLpath As String
PLpath = Range("PLpath")
Workbooks.Open Filename:=PLpath

With Thisworkbook.UserForm1 '<changed this line
.Hide
End With

 

[Kevin]

Hi - didnt work. I also tried to Hide the user form before opening the
workbook but that did not work either. One thing is when I open the user form
the second time, if I just close it (ie click on the red X in top right
corner) and then open it again it seems to reset it ok. Can I write some code
therefore to close the userform instead of hide?

 

[Joel]

You can try an UNLOAD. I think the focus is still on one of the object and
the userform is remembering where it left off. You also can write an
INITILIZE function for the USERFROM so when it is open you can reset
everything in the USERFORM. the right way of soving this problem is to put a
break point (F9) on the SHOW statement and then step through the code using
F8 until you find where it is hanging up.

 

[Kevin]

thank you so much for your help

the Unload seems to work