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:

'
'Saves the New Quote in the template and disables the command button
    ActiveWorkbook.SaveAs ThisWorkbook.Name
    ActiveWorkbook.Sheets("Start Here").Activate
    cmdStartWizard.Enabled = False
    cmdStartWizard.Caption = "Quote Wizard Disabled," & Chr(13) & " please use editing button."
'

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

Private Sub Image4_Click()
'
'
'Selects the quote sheet and activates
    ActiveWorkbook.Sheets("Final Quote").Activate
'
'Changes the Quote Number to Increase by 1
    Range("J4").Value = Range("J4").Value + 1
    Range("J4").Copy
    Range("J4").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False
'
'Inserts the Deliverable Types into Cell J5
'

'
'Inserts the TAT and type into Cell M4, and N4 respectivly
    Sheets("Final Quote").Range("M4").Value = txtTAT.Text
    Sheets("Final Quote").Range("N4").Value = lbxTAType.Text
'
'Inserts the Project Reference into Cell J7
    Sheets("Final Quote").Range("J7").Value = txtProjectReference
'
'Inserts the Company Name into Cell C12
    Sheets("Final Quote").Range("C12").Value = cmbClientList
'
'Saves the file with a unique name
'Declares Variables of the file name for saved as function
    Dim QNum As String
    Dim CNam As String
    Dim CrDt As String
    Dim VNum As String
'
'Defines the variable names from Quote Form
    CNam = Range("C12").Text
    CrDt = Format(Now, "mmddyy")
    VNum = Range("K4").Text
'
'Saves the New Quote in the template and disables the command button
    ActiveWorkbook.SaveAs ThisWorkbook.Name
    ActiveWorkbook.Sheets("Start Here").Activate
    cmdStartWizard.Enabled = False
    cmdStartWizard.Caption = "Quote Wizard Disabled," & Chr(13) & " please use editing button."
'
'Defines the QNum varible to the new quote number after the template is saved to insure they_
'are saved with the same number
    ActiveWorkbook.Sheets("Final Quote").Activate
    QNum = Range("J4").Text
'
'Saves the New Quote as a unique file
    ActiveWorkbook.SaveAs _
    "X:\_FEE SCHEDULE & QUOTE MODULE\Created Quotes\" & QNum & VNum & "-" & CNam _
    & "-" & CrDt
'
'Disables step 1 and Shows step 2
End Sub

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***

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.