Why doesnt this work?
 

Dim MainWorksSheet As Worksheet
Set MainWorksSheet = ThisWorkbook.Worksheets("Main") *Yes the
worksheet is named Main.

MainWorksSheet.CommandButtonAdd.Enabled = True
MainWorksSheet.CommandButtonMakeBusy.Caption = "Start"

**Both being a button created with the control toolbox.

I get the error : Method or Data member not found.

On the other hand :

ActiveSheet.CommandButtonAdd.Enabled = True
ActiveSheet.CommandButtonMakeBusy.Caption = "Start"

Works fine! Unless the focus is on a different sheet named something
else.

I need to make sure that it refers to the sheet "Main" because I have
other sheets and if they have the focus then the ActiveSheet doesnt
work either.
Thanks for any help.

Why doesnt this work?
 

What about trying to set a reference to it using ThisWorkbook? I'm not able
to test this, but it may work:

Dim cmd As CommandBarButton
Set cmd = ThisWorkbook.Sheets("Main").CommandButtonAdd

I've had a similar problem to this when working with named ranges, and I
still haven't found the solution. Hopefully, this workaround might help.

HTH,
Matthew Pfluger

" wrote:

Dim MainWorksSheet As Worksheet
Set MainWorksSheet = ThisWorkbook.Worksheets("Main") *Yes the
worksheet is named Main.

MainWorksSheet.CommandButtonAdd.Enabled = True
MainWorksSheet.CommandButtonMakeBusy.Caption = "Start"

**Both being a button created with the control toolbox.

I get the error : Method or Data member not found.

On the other hand :

ActiveSheet.CommandButtonAdd.Enabled = True
ActiveSheet.CommandButtonMakeBusy.Caption = "Start"

Works fine! Unless the focus is on a different sheet named something
else.

I need to make sure that it refers to the sheet "Main" because I have
other sheets and if they have the focus then the ActiveSheet doesnt
work either.
Thanks for any help.

Why doesnt this work?
 

A worksheet is an object type which does not contain an intrinsically
defined commandbutton. But an object of type <internalSheetName, with the
commandbutton placed on it, does. Activesheet, I guess, conforms to the
sheet referenced including the commandbutton(s). Someone else can explain it
better, but based on the behavior this looks to be the general idea.

If the internal name of Main is Sheet1 for example (viewable in the VBE),
this would work as a workaround.

Dim MainWorksSheet As Sheet1
Set MainWorksSheet = Sheet1
MainWorksSheet.CommandButtonAdd.Enabled = True
MainWorksSheet.CommandButtonMakeBusy.Caption = "Start"

Or just using the external sheet name:

Dim MainWorksSheet As Worksheet
Set MainWorksSheet = ThisWorkbook.Worksheets("Main")
MainWorksSheet.OLEObjects("CommandButtonAdd").Enab led = True
MainWorksSheet.OLEObjects("CommandButtonMakeBusy") .Object.Caption =
"Start"

--
Tim Zych
SF, CA


wrote in message
...
Dim MainWorksSheet As Worksheet
Set MainWorksSheet = ThisWorkbook.Worksheets("Main") *Yes the
worksheet is named Main.

MainWorksSheet.CommandButtonAdd.Enabled = True
MainWorksSheet.CommandButtonMakeBusy.Caption = "Start"

**Both being a button created with the control toolbox.

I get the error : Method or Data member not found.

On the other hand :

ActiveSheet.CommandButtonAdd.Enabled = True
ActiveSheet.CommandButtonMakeBusy.Caption = "Start"

Works fine! Unless the focus is on a different sheet named something
else.

I need to make sure that it refers to the sheet "Main" because I have
other sheets and if they have the focus then the ActiveSheet doesnt
work either.
Thanks for any help.

Why doesnt this work?
 

I'll play dumb and ask if you are running your code from a personal workbook
and trying to set a variable in the active workbook. If so, it won't work
with the ThisWorkbook reference, since ThisWorkbook is the one with the code
running.

" wrote:

Dim MainWorksSheet As Worksheet
Set MainWorksSheet = ThisWorkbook.Worksheets("Main") *Yes the
worksheet is named Main.

MainWorksSheet.CommandButtonAdd.Enabled = True
MainWorksSheet.CommandButtonMakeBusy.Caption = "Start"

**Both being a button created with the control toolbox.

I get the error : Method or Data member not found.

On the other hand :

ActiveSheet.CommandButtonAdd.Enabled = True
ActiveSheet.CommandButtonMakeBusy.Caption = "Start"

Works fine! Unless the focus is on a different sheet named something
else.

I need to make sure that it refers to the sheet "Main" because I have
other sheets and if they have the focus then the ActiveSheet doesnt
work either.
Thanks for any help.

Why doesnt this work?
 

Hi,

I've tried several variations and it doesnt work.
Tim explanation makes sense and explains this weird behaviour.


One last question about this subject just for my future reference.
I have this code that works:

Dim lb As Object
'Add Items to list
Dim X As Long

With Sheets("Main")
Set lb = .ListBoxCross
lb.Clear
lb.AddItem "Not Attempted"
end with

If I was going to use the OLEObjects(" where would it be used?

Thanks

On Feb 9, 9:43*pm, JLGWhiz wrote:
I'll play dumb and ask if you are running your code from a personal workbook
and trying to set a variable in the active workbook. *If so, it won't work
with the ThisWorkbook reference, since ThisWorkbook is the one with the code
running.



" wrote:
Dim MainWorksSheet As Worksheet
Set MainWorksSheet = ThisWorkbook.Worksheets("Main") *Yes the
worksheet is named Main.

MainWorksSheet.CommandButtonAdd.Enabled = True
MainWorksSheet.CommandButtonMakeBusy.Caption = "Start"

**Both being a button created with the control toolbox.

I get the error : Method or Data member not found.

On the other hand :

ActiveSheet.CommandButtonAdd.Enabled = True
ActiveSheet.CommandButtonMakeBusy.Caption = "Start"

Works fine! Unless the focus is on a different sheet named something
else.

I need to make sure that it refers to the sheet "Main" because I have
other sheets and if they have the focus then the ActiveSheet doesnt
work either.
Thanks for any help.