I'm having trouble getting my head round a problem I'm having with some
code.

I have created a userform in a workbook (let's call this workbook1) which
requires users to enter data which is then used to configure a worksheet.
There are 2 worksheets, the one called Lookup is hidden and the forms refers
to this one to find data for comboboxes. The userform then configures the
Machine worksheet and the userform is hidden.

My problem arises (sometimes) when I have workbook1 open, but I am working
on another workbook (say workbook2). I can't remember the exact error I get,
but when I debug, it takes me to one of the lines in the workbook1 form's
code referring to the range in Lookup for a combobox.

I'm guessing the reason I'm getting an error is because workbook2 does not
have a sheet called Lookup.

Can anyone offer a reason for this happeneing? Is there a way to render the
code in the userform inoperative when the form is hidden?

Workbook1 is actually saved as a template so, once the form has finished
taking data and has been hidden, there will never be a use for it again. Can
the entire form be deleted in code?

I could post some of the code, but I'm not sure which bits are relevant.

--
Ian
--

Hi Ian,

Try Posting the relevant code.


---
Regards,
Norman



"Ian" wrote in message
...
I'm having trouble getting my head round a problem I'm having with some
code.

I have created a userform in a workbook (let's call this workbook1) which
requires users to enter data which is then used to configure a worksheet.
There are 2 worksheets, the one called Lookup is hidden and the forms
refers to this one to find data for comboboxes. The userform then
configures the Machine worksheet and the userform is hidden.

My problem arises (sometimes) when I have workbook1 open, but I am working
on another workbook (say workbook2). I can't remember the exact error I
get, but when I debug, it takes me to one of the lines in the workbook1
form's code referring to the range in Lookup for a combobox.

I'm guessing the reason I'm getting an error is because workbook2 does not
have a sheet called Lookup.

Can anyone offer a reason for this happeneing? Is there a way to render
the code in the userform inoperative when the form is hidden?

Workbook1 is actually saved as a template so, once the form has finished
taking data and has been hidden, there will never be a use for it again.
Can the entire form be deleted in code?

I could post some of the code, but I'm not sure which bits are relevant.

--
Ian
--

As I said in my OP, I'm not sure what's relevant, but here's some for
starters.

Private Sub ComboBox1_change()
Select Case ComboBox1
Case "OP100"
ComboBox2.Enabled = True
ComboBox2.Text = "Select"
ComboBox2.RowSource = "Lookup!T3:T4"
ComboBox3.Text = "No"
ComboBox3.Enabled = False
Case "OC100"
ComboBox2.Enabled = True
ComboBox2.Text = "Select"
ComboBox2.RowSource = "Lookup!T3:T4"
ComboBox3.Text = "Yes"
ComboBox3.Enabled = False
Case "OP100OT"
ComboBox2.Enabled = True

This carries on in a similar vein with various other Cases, not always
looking to the same RowSource range. Unfortunately, the Lookup sheet is
stored in a template which then becomes fielname1 on creation of a file and
is saved as another filename which will be unique, so I can't specify Lookup
with a filename. The code in question is on the userform and is not required
once the data has been entered and the worksheet configured. I don't suppose
there's any way to delete a form, rather than hiding it?

--
Ian
--
"Norman Jones" wrote in message
...
Hi Ian,

Try Posting the relevant code.


---
Regards,
Norman



"Ian" wrote in message
...
I'm having trouble getting my head round a problem I'm having with some
code.

I have created a userform in a workbook (let's call this workbook1) which
requires users to enter data which is then used to configure a worksheet.
There are 2 worksheets, the one called Lookup is hidden and the forms
refers to this one to find data for comboboxes. The userform then
configures the Machine worksheet and the userform is hidden.

My problem arises (sometimes) when I have workbook1 open, but I am
working on another workbook (say workbook2). I can't remember the exact
error I get, but when I debug, it takes me to one of the lines in the
workbook1 form's code referring to the range in Lookup for a combobox.

I'm guessing the reason I'm getting an error is because workbook2 does
not have a sheet called Lookup.

Can anyone offer a reason for this happeneing? Is there a way to render
the code in the userform inoperative when the form is hidden?

Workbook1 is actually saved as a template so, once the form has finished
taking data and has been hidden, there will never be a use for it again.
Can the entire form be deleted in code?

I could post some of the code, but I'm not sure which bits are relevant.

--
Ian
--

Hi Ian,

Try qualifying the RowSource assignments, e.g.:

Me.ComboBox2.RowSource = ThisWorkbook.Sheets("Lookup"). _
Range("T3:T4").Address(External:=True)


---
Regards,
Norman



"Ian" wrote in message
...
As I said in my OP, I'm not sure what's relevant, but here's some for
starters.

Private Sub ComboBox1_change()
Select Case ComboBox1
Case "OP100"
ComboBox2.Enabled = True
ComboBox2.Text = "Select"
ComboBox2.RowSource = "Lookup!T3:T4"
ComboBox3.Text = "No"
ComboBox3.Enabled = False
Case "OC100"
ComboBox2.Enabled = True
ComboBox2.Text = "Select"
ComboBox2.RowSource = "Lookup!T3:T4"
ComboBox3.Text = "Yes"
ComboBox3.Enabled = False
Case "OP100OT"
ComboBox2.Enabled = True

This carries on in a similar vein with various other Cases, not always
looking to the same RowSource range. Unfortunately, the Lookup sheet is
stored in a template which then becomes fielname1 on creation of a file
and is saved as another filename which will be unique, so I can't specify
Lookup with a filename. The code in question is on the userform and is not
required once the data has been entered and the worksheet configured. I
don't suppose there's any way to delete a form, rather than hiding it?

--
Ian
--
"Norman Jones" wrote in message
...
Hi Ian,

Try Posting the relevant code.


---
Regards,
Norman



"Ian" wrote in message
...
I'm having trouble getting my head round a problem I'm having with some
code.

I have created a userform in a workbook (let's call this workbook1)
which requires users to enter data which is then used to configure a
worksheet. There are 2 worksheets, the one called Lookup is hidden and
the forms refers to this one to find data for comboboxes. The userform
then configures the Machine worksheet and the userform is hidden.

My problem arises (sometimes) when I have workbook1 open, but I am
working on another workbook (say workbook2). I can't remember the exact
error I get, but when I debug, it takes me to one of the lines in the
workbook1 form's code referring to the range in Lookup for a combobox.

I'm guessing the reason I'm getting an error is because workbook2 does
not have a sheet called Lookup.

Can anyone offer a reason for this happeneing? Is there a way to render
the code in the userform inoperative when the form is hidden?

Workbook1 is actually saved as a template so, once the form has finished
taking data and has been hidden, there will never be a use for it again.
Can the entire form be deleted in code?

I could post some of the code, but I'm not sure which bits are relevant.

--
Ian
--

I assume this is to replace my ComboBox2.RowSource = "Lookup!T3:T4" line,
but what does this mean?
ThisWorkbook I can understand. Me.ComboBox2 has me puzzled. External=True
I'm not sure about, but shouldn't this be false, as the address is in the
same workbook and also in the sheet previously referred to?

--
Ian
--
"Norman Jones" wrote in message
...
Hi Ian,

Try qualifying the RowSource assignments, e.g.:

Me.ComboBox2.RowSource = ThisWorkbook.Sheets("Lookup"). _
Range("T3:T4").Address(External:=True)


---
Regards,
Norman



"Ian" wrote in message
...
As I said in my OP, I'm not sure what's relevant, but here's some for
starters.

Private Sub ComboBox1_change()
Select Case ComboBox1
Case "OP100"
ComboBox2.Enabled = True
ComboBox2.Text = "Select"
ComboBox2.RowSource = "Lookup!T3:T4"
ComboBox3.Text = "No"
ComboBox3.Enabled = False
Case "OC100"
ComboBox2.Enabled = True
ComboBox2.Text = "Select"
ComboBox2.RowSource = "Lookup!T3:T4"
ComboBox3.Text = "Yes"
ComboBox3.Enabled = False
Case "OP100OT"
ComboBox2.Enabled = True

This carries on in a similar vein with various other Cases, not always
looking to the same RowSource range. Unfortunately, the Lookup sheet is
stored in a template which then becomes fielname1 on creation of a file
and is saved as another filename which will be unique, so I can't specify
Lookup with a filename. The code in question is on the userform and is
not required once the data has been entered and the worksheet configured.
I don't suppose there's any way to delete a form, rather than hiding it?

--
Ian
--
"Norman Jones" wrote in message
...
Hi Ian,

Try Posting the relevant code.


---
Regards,
Norman



"Ian" wrote in message
...
I'm having trouble getting my head round a problem I'm having with some
code.

I have created a userform in a workbook (let's call this workbook1)
which requires users to enter data which is then used to configure a
worksheet. There are 2 worksheets, the one called Lookup is hidden and
the forms refers to this one to find data for comboboxes. The userform
then configures the Machine worksheet and the userform is hidden.

My problem arises (sometimes) when I have workbook1 open, but I am
working on another workbook (say workbook2). I can't remember the exact
error I get, but when I debug, it takes me to one of the lines in the
workbook1 form's code referring to the range in Lookup for a combobox.

I'm guessing the reason I'm getting an error is because workbook2 does
not have a sheet called Lookup.

Can anyone offer a reason for this happeneing? Is there a way to render
the code in the userform inoperative when the form is hidden?

Workbook1 is actually saved as a template so, once the form has
finished taking data and has been hidden, there will never be a use for
it again. Can the entire form be deleted in code?

I could post some of the code, but I'm not sure which bits are
relevant.

--
Ian
--

Hi Ian,

To demonstrate, I named two workbooks CodeBook and ActiveBook. In CodeBook,
I ran the following code:
'==========
Public Sub aTest002()
Debug.Print ThisWorkbook.Sheets("Sheet2").Range("T31:T4"). _
Address(External:=True)

Debug.Print Sheets("Sheet2").Range("T3:T4"). _
Address(External:=True)

Debug.Print Range("T3:T4").Address
End Sub

'<<==========

The results returned in the immediate window we

[CodeBook.xls]Sheet2!$T3$1:$T$4
[ActiveBook.xls]Sheet2!$T$3:$T$4
$T$3:$T$4

The last address refers to the T3:T4 range on the active sheet whatever that
may be.

---
Regards,
Norman



"Ian" wrote in message
...
I assume this is to replace my ComboBox2.RowSource = "Lookup!T3:T4" line,
but what does this mean?
ThisWorkbook I can understand. Me.ComboBox2 has me puzzled. External=True
I'm not sure about, but shouldn't this be false, as the address is in the
same workbook and also in the sheet previously referred to?

--
Ian
--
"Norman Jones" wrote in message
...
Hi Ian,

Try qualifying the RowSource assignments, e.g.:

Me.ComboBox2.RowSource = ThisWorkbook.Sheets("Lookup"). _
Range("T3:T4").Address(External:=True)


---
Regards,
Norman



"Ian" wrote in message
...
As I said in my OP, I'm not sure what's relevant, but here's some for
starters.

Private Sub ComboBox1_change()
Select Case ComboBox1
Case "OP100"
ComboBox2.Enabled = True
ComboBox2.Text = "Select"
ComboBox2.RowSource = "Lookup!T3:T4"
ComboBox3.Text = "No"
ComboBox3.Enabled = False
Case "OC100"
ComboBox2.Enabled = True
ComboBox2.Text = "Select"
ComboBox2.RowSource = "Lookup!T3:T4"
ComboBox3.Text = "Yes"
ComboBox3.Enabled = False
Case "OP100OT"
ComboBox2.Enabled = True

This carries on in a similar vein with various other Cases, not always
looking to the same RowSource range. Unfortunately, the Lookup sheet is
stored in a template which then becomes fielname1 on creation of a file
and is saved as another filename which will be unique, so I can't
specify Lookup with a filename. The code in question is on the userform
and is not required once the data has been entered and the worksheet
configured. I don't suppose there's any way to delete a form, rather
than hiding it?

--
Ian
--
"Norman Jones" wrote in message
...
Hi Ian,

Try Posting the relevant code.


---
Regards,
Norman



"Ian" wrote in message
...
I'm having trouble getting my head round a problem I'm having with
some code.

I have created a userform in a workbook (let's call this workbook1)
which requires users to enter data which is then used to configure a
worksheet. There are 2 worksheets, the one called Lookup is hidden and
the forms refers to this one to find data for comboboxes. The userform
then configures the Machine worksheet and the userform is hidden.

My problem arises (sometimes) when I have workbook1 open, but I am
working on another workbook (say workbook2). I can't remember the
exact error I get, but when I debug, it takes me to one of the lines
in the workbook1 form's code referring to the range in Lookup for a
combobox.

I'm guessing the reason I'm getting an error is because workbook2 does
not have a sheet called Lookup.

Can anyone offer a reason for this happeneing? Is there a way to
render the code in the userform inoperative when the form is hidden?

Workbook1 is actually saved as a template so, once the form has
finished taking data and has been hidden, there will never be a use
for it again. Can the entire form be deleted in code?

I could post some of the code, but I'm not sure which bits are
relevant.

--
Ian
--

It is probably because the code refers to Activeworkbook, which is probably
best replaced by Thisworkbook.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ian" wrote in message
...
I'm having trouble getting my head round a problem I'm having with some
code.

I have created a userform in a workbook (let's call this workbook1) which
requires users to enter data which is then used to configure a worksheet.
There are 2 worksheets, the one called Lookup is hidden and the forms
refers
to this one to find data for comboboxes. The userform then configures the
Machine worksheet and the userform is hidden.

My problem arises (sometimes) when I have workbook1 open, but I am working
on another workbook (say workbook2). I can't remember the exact error I
get,
but when I debug, it takes me to one of the lines in the workbook1 form's
code referring to the range in Lookup for a combobox.

I'm guessing the reason I'm getting an error is because workbook2 does not
have a sheet called Lookup.

Can anyone offer a reason for this happeneing? Is there a way to render
the
code in the userform inoperative when the form is hidden?

Workbook1 is actually saved as a template so, once the form has finished
taking data and has been hidden, there will never be a use for it again.
Can
the entire form be deleted in code?

I could post some of the code, but I'm not sure which bits are relevant.

--
Ian
--

The only reference in the code to Active referes to ActiveWindow which
either maximaises or restores the window size. This isn't the problem I'm
getting.

--
Ian
--
"Bob Phillips" wrote in message
...
It is probably because the code refers to Activeworkbook, which is
probably
best replaced by Thisworkbook.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ian" wrote in message
...
I'm having trouble getting my head round a problem I'm having with some
code.

I have created a userform in a workbook (let's call this workbook1) which
requires users to enter data which is then used to configure a worksheet.
There are 2 worksheets, the one called Lookup is hidden and the forms
refers
to this one to find data for comboboxes. The userform then configures the
Machine worksheet and the userform is hidden.

My problem arises (sometimes) when I have workbook1 open, but I am
working
on another workbook (say workbook2). I can't remember the exact error I
get,
but when I debug, it takes me to one of the lines in the workbook1 form's
code referring to the range in Lookup for a combobox.

I'm guessing the reason I'm getting an error is because workbook2 does
not
have a sheet called Lookup.

Can anyone offer a reason for this happeneing? Is there a way to render
the
code in the userform inoperative when the form is hidden?

Workbook1 is actually saved as a template so, once the form has finished
taking data and has been hidden, there will never be a use for it again.
Can
the entire form be deleted in code?

I could post some of the code, but I'm not sure which bits are relevant.

--
Ian
--

Just a thought. If I create a combobox directly in the sheet, there is
LostFocus event. There doesn't appear to be an equivalent in a forms
combobox. I tried the ComboBox1_Exit() option, but this returned a compile
error (Procedure declaration does not match description of event or
procedure having the same name). There is no other instance of Exit anywhere
in my code.

--
Ian
--
"Ian" wrote in message
...
I'm having trouble getting my head round a problem I'm having with some
code.

I have created a userform in a workbook (let's call this workbook1) which
requires users to enter data which is then used to configure a worksheet.
There are 2 worksheets, the one called Lookup is hidden and the forms
refers to this one to find data for comboboxes. The userform then
configures the Machine worksheet and the userform is hidden.

My problem arises (sometimes) when I have workbook1 open, but I am working
on another workbook (say workbook2). I can't remember the exact error I
get, but when I debug, it takes me to one of the lines in the workbook1
form's code referring to the range in Lookup for a combobox.

I'm guessing the reason I'm getting an error is because workbook2 does not
have a sheet called Lookup.

Can anyone offer a reason for this happeneing? Is there a way to render
the code in the userform inoperative when the form is hidden?

Workbook1 is actually saved as a template so, once the form has finished
taking data and has been hidden, there will never be a use for it again.
Can the entire form be deleted in code?

I could post some of the code, but I'm not sure which bits are relevant.

--
Ian
--