|
Code runs in wrong workbook?
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 -- |
Code runs in wrong workbook?
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 -- |
Code runs in wrong workbook?
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 -- |
Code runs in wrong workbook?
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 -- |
Code runs in wrong workbook?
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 -- |
Code runs in wrong workbook?
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 -- |
Code runs in wrong workbook?
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 -- |
Code runs in wrong workbook?
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 -- |
Code runs in wrong workbook?
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 -- |
Code runs in wrong workbook?
Thanks Norman. That's cleared that bit up for me. I'm still puzzled about
the Me reference, but as long as it works... -- Ian -- "Norman Jones" wrote in message ... 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 -- |
Code runs in wrong workbook?
Hi Ian,
See Me in VBA help. In this instance Me refers to the Userform which holds the code. --- Regards, Norman "Ian" wrote in message ... Thanks Norman. That's cleared that bit up for me. I'm still puzzled about the Me reference, but as long as it works... -- Ian -- "Norman Jones" wrote in message ... 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 -- |
Code runs in wrong workbook?
It doesn't appear to be there (XL2002 or 2000). Is this one of the many
instances of the help not being complete (or helpful)? Thanks for the clarification, anyway. I assume Me can be used to localise to the item that holds the code (sheet, book, userform or module). BTW, I used the modified sheet today and it all seemed to run as expected (except for a couple of things I still need to address). At least it didn't try to run the code on the wrong workbook, though it didn't do this consistently anyway. Hopefully that part's all sorted. Many thanks for your help. -- Ian -- "Norman Jones" wrote in message ... Hi Ian, See Me in VBA help. In this instance Me refers to the Userform which holds the code. --- Regards, Norman "Ian" wrote in message ... Thanks Norman. That's cleared that bit up for me. I'm still puzzled about the Me reference, but as long as it works... -- Ian -- "Norman Jones" wrote in message ... 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 -- |
Code runs in wrong workbook?
Hi Ian,
Try placing the cursor in Me in your code and hit F1. You might also like to see Chip Pearson's explanation in a recent post: http://tinyurl.com/dzcyp --- Regards, Norman "Ian" wrote in message ... It doesn't appear to be there (XL2002 or 2000). Is this one of the many instances of the help not being complete (or helpful)? Thanks for the clarification, anyway. I assume Me can be used to localise to the item that holds the code (sheet, book, userform or module). BTW, I used the modified sheet today and it all seemed to run as expected (except for a couple of things I still need to address). At least it didn't try to run the code on the wrong workbook, though it didn't do this consistently anyway. Hopefully that part's all sorted. Many thanks for your help. -- Ian -- "Norman Jones" wrote in message ... Hi Ian, See Me in VBA help. In this instance Me refers to the Userform which holds the code. --- Regards, Norman "Ian" wrote in message ... Thanks Norman. That's cleared that bit up for me. I'm still puzzled about the Me reference, but as long as it works... -- Ian -- "Norman Jones" wrote in message ... 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 -- |
Code runs in wrong workbook?
Thanks for that, Norman. I have to admit, your & Chip's explanations beat
VBA help hands down! I think I understand now :-) -- Ian -- "Norman Jones" wrote in message ... Hi Ian, Try placing the cursor in Me in your code and hit F1. You might also like to see Chip Pearson's explanation in a recent post: http://tinyurl.com/dzcyp --- Regards, Norman "Ian" wrote in message ... It doesn't appear to be there (XL2002 or 2000). Is this one of the many instances of the help not being complete (or helpful)? Thanks for the clarification, anyway. I assume Me can be used to localise to the item that holds the code (sheet, book, userform or module). BTW, I used the modified sheet today and it all seemed to run as expected (except for a couple of things I still need to address). At least it didn't try to run the code on the wrong workbook, though it didn't do this consistently anyway. Hopefully that part's all sorted. Many thanks for your help. -- Ian -- "Norman Jones" wrote in message ... Hi Ian, See Me in VBA help. In this instance Me refers to the Userform which holds the code. --- Regards, Norman "Ian" wrote in message ... Thanks Norman. That's cleared that bit up for me. I'm still puzzled about the Me reference, but as long as it works... -- Ian -- "Norman Jones" wrote in message ... 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 -- |
| All times are GMT +1. The time now is 05:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com