Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Userform the second time
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 -- Kevin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Userform the second time
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" wrote: 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 -- Kevin |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Userform the second time
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? -- Kevin "Joel" wrote: 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" wrote: 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 -- Kevin |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Userform the second time
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" wrote: 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? -- Kevin "Joel" wrote: 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" wrote: 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 -- Kevin |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Userform the second time
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? -- Kevin "Joel" wrote: 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" wrote: 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? -- Kevin "Joel" wrote: 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" wrote: 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 -- Kevin |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Userform the second time
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" wrote: 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? -- Kevin "Joel" wrote: 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" wrote: 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? -- Kevin "Joel" wrote: 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" wrote: 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 -- Kevin |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Userform the second time
thank you so much for your help
the Unload seems to work -- Kevin "Joel" wrote: 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" wrote: 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? -- Kevin "Joel" wrote: 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" wrote: 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? -- Kevin "Joel" wrote: 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" wrote: 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 -- Kevin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
display userform at certain time | Excel Worksheet Functions | |||
time userform 12:00 changes in 00:05 | Excel Programming | |||
Userform from a Userform Problem | Excel Programming | |||
Time Userform | Excel Programming | |||
Problem when multipple users access shared xl-file at the same time, macrocode for solve this problem? | Excel Programming |