Specifying a Command Button on a worksheet as differentiated from one on a UserForm

Well, most of the coding for the new quote module user form has been going well. I have however, hit a snag. In the code below there is a section that is causing an error. The debugger is saying the varible is not defined. However, where it is stopping is not exactly a varible. It is the name of a command button placed on the sheet mentioned in the same line. Other than having the code select the right sheet first (already incorporated into the code) I don't know how else to tell VBA that it is a command button on the sheet and not one on the UserForm.

Any help will be appreciated.

The offending code is as follows:

Please Login or Register  to view this content.

And this is it in context of the entire click event:

Please Login or Register  to view this content.

Here's to hope,
Amber

Hello Amber_D_Laws,

You can't reference a command button on worksheet the same way you reference it on a UserForm. There are 2 types of command buttons and you don't which you are using, so i will include the referencing methods for both.

Forms Toolbar Command Button Example:

With ActiveSheet.Shapes("Button 1")
.TextFrame.Characters.Text = "Quote Wizard Disabled" & vbLf & "please use editing button"
.ControlFormat.Enabled = False
End With


Control Toolbox Command Button Example:

Dim cmdButton As Object
Set cmdButton = ActiveSheet.OLEObjects("CommandButton1").Object
cmdButton.Caption = "Quote Wizard Disabled" & vbLf & "please use editing button"
cmdButton.Enabled = False


Change the command buttons in the examples to their original code names. This is not the same as the caption. This is the name the system used when they were created.

Sincerely,
Leith Ross

Thanks Leith,

It is a Toolbox Command Button, I think. How do I tell the difference to be sure? And by:

Originally Posted by Leith Ross

Change the command buttons in the examples to their original code names. This is not the same as the caption. This is the name the system used when they were created.

do you mean that I should change it's name from cmdStartWiard back to CommandButton1? I ask because I thought that once a name (as in its (Name) from the properties) was changed you could reference that in code without issue. Is this not always the case?

Originally Posted by Leith Ross

Hello Amber_D_Laws,

You can't reference a command button on worksheet the same way you reference it on a UserForm. There are 2 types of command buttons and you don't which you are using, so i will include the referencing methods for both.

Forms Toolbar Command Button Example:

With ActiveSheet.Shapes("Button 1")
.TextFrame.Characters.Text = "Quote Wizard Disabled" & vbLf & "please use editing button"
.ControlFormat.Enabled = False
End With


Control Toolbox Command Button Example:

Dim cmdButton As Object
Set cmdButton = ActiveSheet.OLEObjects("CommandButton1").Object
cmdButton.Caption = "Quote Wizard Disabled" & vbLf & "please use editing button"
cmdButton.Enabled = False


Change the command buttons in the examples to their original code names. This is not the same as the caption. This is the name the system used when they were created.

Sincerely,
Leith Ross

Hello Amber_D_Laws,

If you created the command button from the Excel menu then it is a Forms type. If you created it using the VBA Toolbar then it is a Control Toolbox type. If you still aren't sure, try each code version.

You are correct about the name needing to be changed to CommandButton1. When you change the name of the control, it only effects the project references, not the system reference.

Sincerely,
Leith Ross

Before you get all wrapped around the axle,

It sounds like Leith's information is a bit dated. there was a problem with
this in xl97, so if you are using xl97 it is a bit more complex, but you can
still change the code name of the commandbutton. I assume you are not
working with excel 97 and as your subject states, this code is run from a
userform module. In that case the unqualified name cmdStartWizard is seen
to refer to the Userform where there is no such control.

It sounds like the only thing you need to do is qualify the name of the
command button with a reference to the sheet so Excle knows where it is.

Activesheet.cmdStartWizard.Enabled = False
ActiveSheet.cmdStartWizard.Caption = "Quote Wizard Disabled," & Chr(13) & "
please use editing

-------------- if it isn't the activesheet, then assume it is on Sheet3


Worksheets("Sheet3").Activesheet.cmdStartWizard.Enabled = False
Worksheets("Sheet3").ActiveSheet.cmdStartWizard.Caption = "Quote Wizard
Disabled," & Chr(13) & " please use editing

---------------------------

Here is a little demo from the immediate window to prove the point. I have
one button on Sheet1 named CommandButton1

' check the name by displaying the caption
? Activesheet.CommandButton1.Caption
CommandButton1

'Change the name to cmdStartWizard
Activesheet.CommandButton1.Name = "cmdStartWizard"

' Now use the new name to check the caption
? activesheet.cmdStartWizard.Caption
CommandButton1

' Now use the new name to work with the control
' by changing the caption
activesheet.cmdStartWizard.Caption = "HokeyPokey"

' Verify that it is changed
? activesheet.cmdStartWizard.Caption
HokeyPokey

--
Regards,
Tom Ogilvy

"Leith Ross" <Leith.Ross.22mbqo_1138911006.0467@excelforum-nospam.com> wrote
in message news:Leith.Ross.22mbqo_1138911006.0467@excelforum-nospam.com...
>
> Hello Amber_D_Laws,
>
> If you created the command button from the Excel menu then it is a
> Forms type. If you created it using the VBA Toolbar then it is a
> Control Toolbox type. If you still aren't sure, try each code version.
>
> You are correct about the name needing to be changed to CommandButton1.
> When you change the name of the control, it only effects the project
> references, not the system reference.
>
> Sincerely,
> Leith Ross
>
>
> --
> Leith Ross
> ------------------------------------------------------------------------
> Leith Ross's Profile:
http://www.excelforum.com/member.php...o&userid=18465
> View this thread: http://www.excelforum.com/showthread...hreadid=507754
>

Leith,

I tried both versions, and got two diffrent error messages with each. I.E. neither worked. I had already changed the name back to CommandButton1, but but I went back and double check just in case. Still no dice. I will try Tom's suggestion and let you both know how it goes.

Thanks,
Amber

Originally Posted by Leith Ross

Hello Amber_D_Laws,

If you created the command button from the Excel menu then it is a Forms type. If you created it using the VBA Toolbar then it is a Control Toolbox type. If you still aren't sure, try each code version.

You are correct about the name needing to be changed to CommandButton1. When you change the name of the control, it only effects the project references, not the system reference.

Sincerely,
Leith Ross

**** Bump ****

As far as I can tell, there are no pending questions in this thread? What
is unanswered.

--
Regards,
Tom Ogilvy


"Amber_D_Laws" <Amber_D_Laws.22npyy_1138976101.2853@excelforum-nospam.com>
wrote in message
news:Amber_D_Laws.22npyy_1138976101.2853@excelforum-nospam.com...
>
> **** Bump ****
>
>
> --
> Amber_D_Laws
> ------------------------------------------------------------------------
> Amber_D_Laws's Profile:
http://www.excelforum.com/member.php...o&userid=30012
> View this thread: http://www.excelforum.com/showthread...hreadid=507754
>

Tom,

That's ok, there was a lot going on. Please see the quote below....

I tried your suggestion and I got the error message of "object doesn't support this property or method" highlighted on the line "Worksheets("Start Here").ActiveSheet.cmdStartWizard.Enabled = False"
Any advice as to what is going on?

Hope to hear something soon,
Amber

Originally Posted by Tom Ogilvy

As far as I can tell, there are no pending questions in this thread? What
is unanswered.

--
Regards,
Tom Ogilvy


"Amber_D_Laws" <Amber_D_Laws.22npyy_1138976101.2853@excelforum-nospam.com>
wrote in message
news:Amber_D_Laws.22npyy_1138976101.2853@excelforum-nospam.com...
>
> **** Bump ****
>
>
> --
> Amber_D_Laws
> ------------------------------------------------------------------------
> Amber_D_Laws's Profile:
http://www.excelforum.com/member.php...o&userid=30012
> View this thread: http://www.excelforum.com/showthread...hreadid=507754
>

Apparently there was a typo in my original post.

My 2nd suggested form should have been:

Worksheets("Start Here").cmdStartWizard.Enabled = False

Apparently when I edited a copy of the first suggested format, I didn't
remove the Activesheet qualification.

--
Regards,
Tom Ogilvy





"Amber_D_Laws" <Amber_D_Laws.22o6eo_1138997408.5922@excelforum-nospam.com>
wrote in message
news:Amber_D_Laws.22o6eo_1138997408.5922@excelforum-nospam.com...
>
> Tom,
>
> That's ok, there was a lot going on. Please see the quote below....
>
> > I tried your suggestion and I got the error message of "object doesn't
> > support this property or method" highlighted on the line
> > "Worksheets("Start Here").ActiveSheet.cmdStartWizard.Enabled = False"
> > Any advice as to what is going on?
>
> Hope to hear something soon,
> Amber
>
>
> Tom Ogilvy Wrote:
> > As far as I can tell, there are no pending questions in this thread?
> > What
> > is unanswered.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Amber_D_Laws"
> > <Amber_D_Laws.22npyy_1138976101.2853@excelforum-nospam.com>
> > wrote in message
> > news:Amber_D_Laws.22npyy_1138976101.2853@excelforum-nospam.com...
> > >
> > > **** Bump ****
> > >
> > >
> > > --
> > > Amber_D_Laws
> > >
> > ------------------------------------------------------------------------
> > > Amber_D_Laws's Profile:
> > http://www.excelforum.com/member.php...o&userid=30012
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=507754
> > >
>
>
> --
> Amber_D_Laws
> ------------------------------------------------------------------------
> Amber_D_Laws's Profile:
http://www.excelforum.com/member.php...o&userid=30012
> View this thread: http://www.excelforum.com/showthread...hreadid=507754
>

Hi Tom,

I tried the code below as you suggested:

Worksheets("Start Here").cmdStartWizard.Enabled = False

and I am still getting the "object doesn't support this property or method" error messgage. I don't understand why it isn't working.

Thanks again,
Amber

Originally Posted by Tom Ogilvy

Apparently there was a typo in my original post.

My 2nd suggested form should have been:

Worksheets("Start Here").cmdStartWizard.Enabled = False

Apparently when I edited a copy of the first suggested format, I didn't
remove the Activesheet qualification.

--
Regards,
Tom Ogilvy





"Amber_D_Laws" <Amber_D_Laws.22o6eo_1138997408.5922@excelforum-nospam.com>
wrote in message
news:Amber_D_Laws.22o6eo_1138997408.5922@excelforum-nospam.com...
>
> Tom,
>
> That's ok, there was a lot going on. Please see the quote below....
>
> > I tried your suggestion and I got the error message of "object doesn't
> > support this property or method" highlighted on the line
> > "Worksheets("Start Here").ActiveSheet.cmdStartWizard.Enabled = False"
> > Any advice as to what is going on?
>
> Hope to hear something soon,
> Amber
>
>
> Tom Ogilvy Wrote:
> > As far as I can tell, there are no pending questions in this thread?
> > What
> > is unanswered.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Amber_D_Laws"
> > <Amber_D_Laws.22npyy_1138976101.2853@excelforum-nospam.com>
> > wrote in message
> > news:Amber_D_Laws.22npyy_1138976101.2853@excelforum-nospam.com...
> > >
> > > **** Bump ****
> > >
> > >
> > > --
> > > Amber_D_Laws
> > >
> > ------------------------------------------------------------------------
> > > Amber_D_Laws's Profile:
> > http://www.excelforum.com/member.php...o&userid=30012
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=507754
> > >
>
>
> --
> Amber_D_Laws
> ------------------------------------------------------------------------
> Amber_D_Laws's Profile:
http://www.excelforum.com/member.php...o&userid=30012
> View this thread: http://www.excelforum.com/showthread...hreadid=507754
>

I had a thought. What if the command button on the worksheet was the object that was driving the code, so that after the workbook is saved it changes itself as opposed to the user form changing it which is what I am trying to do now. Would this be easier, or harder?

Hi Tom,

I tried the code below as you suggested:

Worksheets("Start Here").cmdStartWizard.Enabled = False

and I am still getting the "object doesn't support this property or method" error messgage. I don't understand why it isn't working.

Thanks again,
Amber



Quote:
Originally Posted by Tom Ogilvy
Apparently there was a typo in my original post.

My 2nd suggested form should have been:

Worksheets("Start Here").cmdStartWizard.Enabled = False

Apparently when I edited a copy of the first suggested format, I didn't
remove the Activesheet qualification.

--
Regards,
Tom Ogilvy

***Bump***

Any help is appreciated. Tom has given me great ideas, I just understand why it still isn't working.

So, Tom if your reading this....Any ideas.
or
If not Tom please see if you can find something that we missed.

Thanks in advance.

For two days I have been banging my head against the keyboard trying to find out why Tom's perfectly resonable solution to my problem was not working out.

Well....As it turns out while using someone elses advice, I had changed the object's name back to CommandButton1, and so, when I changed the code to go along with Tom's advice, I forgot to change the button's name back to what I wanted it to be (as it was used in the code). So, of course VBA didn't recognize the object!!!!! There was no cmdStartWizard as far as it was concerned.

Eeesh! makes me so mad at myself for pestering Tom and it being my own idiocy the was causing the problem.

So, this is my offical appology for seeming to be a pest about something that was not really a problem at all. Thank you Tom for being as patient as you were, and for giving me the help that in the end worked briliantly and saved the day!

I am sure that I will continue to have questions as I face the next three pages of this multi-page user form. I just hope that my carelessness will not prevent others from helping when I need it. I would hate to be known as "The Girl that cried Error".

Regards,
Amber

Originally Posted by Amber_D_Laws

***Bump***

Any help is appreciated. Tom has given me great ideas, I just understand why it still isn't working.

So, Tom if your reading this....Any ideas.
or
If not Tom please see if you can find something that we missed.

Thanks in advance.