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

[Amber_D_Laws]

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:


Code:
--------------------
'
'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:


Code:
--------------------
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

 

[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

 

[Amber_D_Laws]

Thanks Leith,

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

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?

 

[Leith Ross]

Hello Amber_D_Laws,

If you created the command button from the Excel menu then it is
Forms type. If you created it using the VBA Toolbar then it is
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 projec
references, not the system reference.

Sincerely,
Leith Ros

 

[Tom Ogilvy]

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

 

[Amber_D_Laws]

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

 

[Amber_D_Laws]

Tom,

Love the "Hokey Pokey" caption in the example, it made me laugh righ
out loud much to the confusion of my co-workers. I will try you
suggestion and let you all know how it goes from there.

Thanks again!
Amber

 

[Amber_D_Laws]

**** Bump ***

 

[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" <[email protected]>
wrote in message
news:[email protected]...

 

[Amber_D_Laws]

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]

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" <[email protected]>
wrote in message

 

[Amber_D_Laws]

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 o
method" error messgage. I don't understand why it isn't working.

Thanks again,
Amber

 

[Amber_D_Laws]

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?

 

[Amber_D_Laws]

***Bump***

Any help is appreciated. Tom has given me great ideas, I jus
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

 

[Amber_D_Laws]

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

Well....As it turns out while using someone elses advice, I had change
the object's name back to CommandButton1, and so, when I changed th
code to go along with Tom's advice, I forgot to change the button'
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 n
cmdStartWizard as far as it was concerned.

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

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

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

Regards,
Amber