How to activate either command button depending on cell value?

[albertc30]

Hi all.

It's been a while since my last post. As usual, here I am, back with more questions and still as dumb as a donkey! I'll never grasp this coding thing I don't think.

Anyways, to the chase. I have a field with a dropdown list that will have either Invoice or proforma. Consequently I have two command buttons one for Invoices and the other for proforma.

Depending on what's selected on the cell I want the correct button to become active. I have used the following code and more, but to no avail.
Trust me, I have been at it for the past 2 hours almost.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Range("E3").Value = "Invoice" Then
        Sheet1.CBProformas.Enabled = False
        Sheet1.CBInvoices.Enabled = True
    Else
        Sheet1.CBProformas.Enabled = True
        Sheet1.CBInvoices.Enabled = False
    End If

End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Range("E3").Value = "Invoice" Then
        Sheet1.CBProformas.Enabled = False
        Sheet1.CBInvoices.Enabled = True
    Else
        Sheet1.CBProformas.Enabled = True
        Sheet1.CBInvoices.Enabled = False
    End If
End Sub

I have used either of the above but I'm out of luck and tiered. I'm sure, as usual this is basic stuff but I'm out of ideas.

As usual, any help is always truly appreciated.

Regards,
Albert

 

[Trebor76]

Hi albertc30,

What kind of buttons (controls) are you using? The above will only work on ActiveX controls not Form controls.

Why not just have one generic button that runs the relevant code based on the selection made in cell E3?

Hope that helps,

Robert

 

[My Aswer Is This]

Normally when we use a sheet change event we do not need to click a button.
So why not do something like this:
In Range("E3").value="Invoice" do this

And the do this code will be in the sheet event code not in a Button.

 

[albertc30]

Hi albertc30,

What kind of buttons (controls) are you using? The above will only work on ActiveX controls not Form controls.

Why not just have one generic button that runs the relevant code based on the selection made in cell E3?

Hope that helps,

Robert

Hi Robert.

The button is an ActiveX control. It's a command button.

I sort of seen this suggestion coming a mile a way and yes I guess I could do it that way.

Initially my thought process was to have each button assigned it's own code macro to run and make it simpler.

To the other suggestion made by the other user, I guess then the sheet event is out of the question then. Makes sense.

Much appreciated for your help as always.

Now where do I go from here? Something in the lines of;

if cell.e3 "invoice" then
Save cell.h2 'needs saving data in invoice sheet
Else
Cell.e3 "proforma" then
Save cell.h2 'needs saving data in proforma sheet.

There will be much more to add to that but getting this to work would be a start.

Many thanks everyone.

 

[Trebor76]

You can use this as an idea:

Option Explicit
Sub Macro1()

    Select Case StrConv(Range("E3"), vbLowerCase)
        Case Is = "invoice"
            'Code here for 'invoice' option
        Case Is = "proforma"
            'Code here for 'proforma' option
    End Select

End Sub

Robert

 

[albertc30]

Thank you.

Really simple and makes me look like useless.

I've been told to over complicate things. My mind's buzzing.

Will try it when I get home.

Much appreciated as always.
Thank you.

Ps. I'll try and add a msgbox which will alert user to the fact of what's been chosen and the option to cancel it before going any further.

 

[My Aswer Is This]

A worksheet change event script will work for what you want.

Just tell us what you want to happen if the Range("E3")
Equals "

Invoice"

And what you want to happen if Range("E3") equals

"proforma"

Tell us exactly what you want. Do not say click this button or click that button.

Show us the script you have in those buttons​

​

 

[albertc30]

A worksheet change event script will work for what you want.

Just tell us what you want to happen if the Range("E3")
Equals "
Invoice"

And what you want to happen if Range("E3") equals
"proforma"



Tell us exactly what you want. Do not say click this button or click that button.



Show us the script you have in those buttons

Hi.

I have had nothing much other than the code I have posted previously I'm afraid.

This is starting from scratch and the intention was to have each button running their own code to either generate the invoice or proforma.

The main sheet where these buttons are is called Invoice/Proforma, and all the fields will need populating like customers names, address, products etc...

Sorry if I have inadvertently hit a nerve.

Regards,
Albert.

 

[Trebor76]

I'll try and add a msgbox which will alert user to the fact of what's been chosen and the option to cancel it before going any further.

So maybe this:

Option Explicit
Sub Macro1()

    If MsgBox("You have selected the option """ & Range("E3") & """." & vbNewLine & "Is this correct?", vbQuestion + vbYesNo) = vbNo Then
        Exit Sub
    End If

    Select Case StrConv(Range("E3"), vbLowerCase)
        Case Is = "invoice"
            'Code here for 'invoice' option
        Case Is = "proforma"
            'Code here for 'proforma' option
    End Select

End Sub

Do not say click this button or click that button.

Didn't Albert say he had two buttons from his first post

Consequently I have two command buttons one for Invoices and the other for proforma.

 

[My Aswer Is This]

Hi.

I have had nothing much other than the code I have posted previously I'm afraid.

This is starting from scratch and the intention was to have each button running their own code to either generate the invoice or proforma.

The main sheet where these buttons are is called Invoice/Proforma, and all the fields will need populating like customers names, address, products etc...

Sorry if I have inadvertently hit a nerve.

Regards,
Albert.

Not sure why you think you hit a nerve.
All I'm trying to say is when we use sheet change event scripts we do not write them to click certain buttons.
We tell the script to do what is in the Button script.

Would you please show us the code you have in these two Buttons.