Run-time 438 - OptionButton troubles
in [Programming]
|
Prev: Excel AddIn - how to get formula from Formula Dialog
Next: Generating an RGB color spectrum based on cell values (XL03)
From: Luke on 19 Feb 2010 17:46 I've had this trouble in various different bits of code and can't figure out how to correct it. Here's the situation: I've selected an item from a ListBox contained within a UserForm. The UserForm appears on sheet "A". Once I select the item, Excel gets busy populating a whole bunch of stuff on sheet "B". In the process it is supposed to check to see if the value of OptionButton1 on sheet "B" is "True". The bit of code that it has trouble with is this: If Worksheets("B").Shapes("OptionButton1").Value = True Then ' do stuff At first, I had "OLEObjects" in the place of "Shapes", but it didn't like that either. How do I go about checking the value of an ActiveX control embedded on a worksheet, regardless of whether the worksheet is active?
From: Luke on 19 Feb 2010 18:03 I forgot to say, the error it is giving me says "Object doesn't support this property or method". Thanks. "Luke" wrote: > I've had this trouble in various different bits of code and can't figure out > how to correct it. Here's the situation: > > I've selected an item from a ListBox contained within a UserForm. The > UserForm appears on sheet "A". Once I select the item, Excel gets busy > populating a whole bunch of stuff on sheet "B". In the process it is > supposed to check to see if the value of OptionButton1 on sheet "B" is > "True". The bit of code that it has trouble with is this: > If Worksheets("B").Shapes("OptionButton1").Value = True Then > ' do stuff > > At first, I had "OLEObjects" in the place of "Shapes", but it didn't like > that either. How do I go about checking the value of an ActiveX control > embedded on a worksheet, regardless of whether the worksheet is active?
From: Dave Peterson on 19 Feb 2010 18:11 I'd try: If Worksheets("B").OptionButton1.Value = True Then or if you want to use the OLEObjects collection: If Worksheets("B").oleobjects("OptionButton1").object.Value = True Then Luke wrote: > > I've had this trouble in various different bits of code and can't figure out > how to correct it. Here's the situation: > > I've selected an item from a ListBox contained within a UserForm. The > UserForm appears on sheet "A". Once I select the item, Excel gets busy > populating a whole bunch of stuff on sheet "B". In the process it is > supposed to check to see if the value of OptionButton1 on sheet "B" is > "True". The bit of code that it has trouble with is this: > If Worksheets("B").Shapes("OptionButton1").Value = True Then > ' do stuff > > At first, I had "OLEObjects" in the place of "Shapes", but it didn't like > that either. How do I go about checking the value of an ActiveX control > embedded on a worksheet, regardless of whether the worksheet is active? -- Dave Peterson
From: Luke on 19 Feb 2010 21:34 I had tried the first suggestion before, but to no avail. However, your second suggestion worked like a charm. Thank you so much. This will save me no end of headaches. "Dave Peterson" wrote: > I'd try: > > If Worksheets("B").OptionButton1.Value = True Then > > or if you want to use the OLEObjects collection: > > If Worksheets("B").oleobjects("OptionButton1").object.Value = True Then > > > > Luke wrote: > > > > I've had this trouble in various different bits of code and can't figure out > > how to correct it. Here's the situation: > > > > I've selected an item from a ListBox contained within a UserForm. The > > UserForm appears on sheet "A". Once I select the item, Excel gets busy > > populating a whole bunch of stuff on sheet "B". In the process it is > > supposed to check to see if the value of OptionButton1 on sheet "B" is > > "True". The bit of code that it has trouble with is this: > > If Worksheets("B").Shapes("OptionButton1").Value = True Then > > ' do stuff > > > > At first, I had "OLEObjects" in the place of "Shapes", but it didn't like > > that either. How do I go about checking the value of an ActiveX control > > embedded on a worksheet, regardless of whether the worksheet is active? > > -- > > Dave Peterson > . >
From: Dave Peterson on 20 Feb 2010 08:36
Both worked ok for me. Luke wrote: > > I had tried the first suggestion before, but to no avail. However, your > second suggestion worked like a charm. Thank you so much. This will save me > no end of headaches. > > "Dave Peterson" wrote: > > > I'd try: > > > > If Worksheets("B").OptionButton1.Value = True Then > > > > or if you want to use the OLEObjects collection: > > > > If Worksheets("B").oleobjects("OptionButton1").object.Value = True Then > > > > > > > > Luke wrote: > > > > > > I've had this trouble in various different bits of code and can't figure out > > > how to correct it. Here's the situation: > > > > > > I've selected an item from a ListBox contained within a UserForm. The > > > UserForm appears on sheet "A". Once I select the item, Excel gets busy > > > populating a whole bunch of stuff on sheet "B". In the process it is > > > supposed to check to see if the value of OptionButton1 on sheet "B" is > > > "True". The bit of code that it has trouble with is this: > > > If Worksheets("B").Shapes("OptionButton1").Value = True Then > > > ' do stuff > > > > > > At first, I had "OLEObjects" in the place of "Shapes", but it didn't like > > > that either. How do I go about checking the value of an ActiveX control > > > embedded on a worksheet, regardless of whether the worksheet is active? > > > > -- > > > > Dave Peterson > > . > > -- Dave Peterson |