A
anon
Hi,
I have a wb with 11 user forms - the forms are linked to one another
through the use of command buttons.
My first form (userform1) is a calendar (dynamic control sources set
for textboxes relating to a ws in my wb where the calendar entries are
stored). This userform has a command button which hides userform1 and
opens userform2. Userform2 has some buttons to run some code. At the
end of this code userform2 is hidden and userform1 is shown again.
Userform1 has userfrom_activate code that then runs.
This has worked fine for approx 1 week, however now I am getting run
time errors in the code that re-opens userform1 after userform2 is
hidden. From some research I believe this is due to unreleased object
references.
The error I am currently getting (although I have had other similar
ones at the same point) is;
Run-time error '-2147417848' Method 'select' of object 'range' failed.
I've read the MS help article on "Excel automation fails second time
code runs". If I am understanding correctly this is because the code
my userform2 is running is setting a reference to an object variable
and then not releasing it - then when userform1 is re-activated (and
the userform_activate code runs) this causes a problem. However I am
not clear on how to fix this issue.
Is referring explicitly to my objects enough;
activeworkbook.sheets("sheet1") instead of (sheets("sheet1") which is
how I have written my code)
OR
do I need to set a variable to the worksheet and wb;
dim xlsheet as excel.worksheet
dim xlbook as excel.workbook
set xlbook = "Planner.xls"
set xlsheet as xlbook.worksheets("sheet1")
and then set xlsheet as nothing
etc etc at the end of each sub?
Also does this apply to the way i refer to my userforms (currently I
have simply referred to them;
userform1.controls("Textbox1")
etc etc.
I'd appreciate any help on this as I am tearing my hair out?
I have a wb with 11 user forms - the forms are linked to one another
through the use of command buttons.
My first form (userform1) is a calendar (dynamic control sources set
for textboxes relating to a ws in my wb where the calendar entries are
stored). This userform has a command button which hides userform1 and
opens userform2. Userform2 has some buttons to run some code. At the
end of this code userform2 is hidden and userform1 is shown again.
Userform1 has userfrom_activate code that then runs.
This has worked fine for approx 1 week, however now I am getting run
time errors in the code that re-opens userform1 after userform2 is
hidden. From some research I believe this is due to unreleased object
references.
The error I am currently getting (although I have had other similar
ones at the same point) is;
Run-time error '-2147417848' Method 'select' of object 'range' failed.
I've read the MS help article on "Excel automation fails second time
code runs". If I am understanding correctly this is because the code
my userform2 is running is setting a reference to an object variable
and then not releasing it - then when userform1 is re-activated (and
the userform_activate code runs) this causes a problem. However I am
not clear on how to fix this issue.
Is referring explicitly to my objects enough;
activeworkbook.sheets("sheet1") instead of (sheets("sheet1") which is
how I have written my code)
OR
do I need to set a variable to the worksheet and wb;
dim xlsheet as excel.worksheet
dim xlbook as excel.workbook
set xlbook = "Planner.xls"
set xlsheet as xlbook.worksheets("sheet1")
and then set xlsheet as nothing
etc etc at the end of each sub?
Also does this apply to the way i refer to my userforms (currently I
have simply referred to them;
userform1.controls("Textbox1")
etc etc.
I'd appreciate any help on this as I am tearing my hair out?