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

 

[albertc30]

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

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

Thanks.

It's a start. I did had two buttons as I was thinking to have the code replicated and whatever changes required depending on the option chosen. Having said that I guess I can have just the same button but user will have to select what document they want to create and then be asked upon the document they wish to produce, then the code will follow.

I apologize for the delay in getting back but been sick with a terrible cold just before xmas time and I'm just about over it.

A happy new year to all.

Thanks for helping.

 

[albertc30]

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.

I'm moving away from the two buttons. User will be asked when clicking the ONLY button now as to which document they have selected from the drop down menu and if incorrect they can always press cancel and rectify.
The previous idea of two buttons was to give me a better and clear of the code without having to replicate most of it with only a few changes depending on option selected either Invoice or Proforma.

Many thanks for your input and trying to help as it is much appreciated.

This has been a long going small project for a friend which I have been helping on my own free time.

The only thing I get out of it is the joy of trying to learn some codding but at 42 and having left IT behind me (20 years of networks and communications), I think it's too late to learn codding.

I am now after 3 years back in college became an electrician and install day in day out electric and gas meters. It's been the best change I ever made or had I stayed in networking I'd be divorced by now.

Again, many thanks for all your help as all I have ever achieved in this ongoing little project has greatly happened because of you all. So your time given in helping me is truly appreciated.

I'm sure I will be back soon with more questions. Meanwhile I will try and implement changes on code and see where I get stuck. Tons of google searches.

Once again, many thanks.

 

[albertc30]

Hi all.

To satisfy everyone's curiosity, bellow is the current code that creates the invoice document.

Nothing has been changed in order to create either an invoice or a proforma. This is the code that I am to tinker with.

The code will now look at cell E3 to see what has been chosen either Invoice or proforma and then if invoice then code runs as is, if proforma then code will be changed to safe data in proforma sheet and a few other things, but almost behaving as in the invoice code.

Off course the invoice or proforma number is chosen with a formula currently testing "=IF(E3="Invoice",MAX(Invoices!A:A)+1,IF(E3="Proforma",MAX(Proformas!A:A)+1))".

Only time will tell how this will go.

  Sub Check_Info()
    Dim i As Long, D, E
       D = Array("Original", "Duplicate", "Triplicate")
       E = Array("- Customer Copy -", "- Customer Paid Copy -", "- Accounts Copy -")
    
    If Range("AS1") = Empty Then 'Checks if customer has been selected.
        MsgBox "No Customer selected!", vbInformation, "Customer..."
        Range("AS1").Select
    Else
    If Range("W17") = Empty Then 'Checks if there is a delivery date.
        MsgBox "Please add a delivery date!", vbInformation, "Delivery date..."
        Range("W17").Select
                
        Else
               
        If Range("w17").Value < Date Then
            answer = MsgBox("The delivery date is set in the past." & vbNewLine & "Click OK if date is correct." & vbNewLine & "Click Cancel to change.", vbQuestion + vbOKCancel, "Delivery date!")
            If answer = vbCancel Then
                Range("w17").Select
                Exit Sub
            End If
        End If
        
    'Else
    If Range("AX17") = Empty Then 'Checks if there is who processed invoice.
        MsgBox "Please select Processed By!", vbInformation, "Processed by..."
        Range("AX17").Select
    Else
    If Range("AZ73").Value = 0 Then
        MsgBox "Invoice cannot be £0.00!", vbInformation, "Invoice total..." 'Checks invoice total must be greater than 0.
    Else
        
        'bellow is code to rename file if it existes.
    Dim sfile
    'sfile = "E:\Lusa\Generated Invoices\INV" & Range("L17").Text & ".pdf"
    'If Dir(sfile) <> "" Then Name sfile As Replace(sfile, ".pdf", Format(Now(), "yy-mm-dd-hh-mm.pdf"))
    sfile = "D:\Lusa\Generated Invoices\INV" & Range("L17").Text & ".pdf"
    If Dir(sfile) <> "" Then Name sfile As Replace(sfile, ".pdf", Format(Now(), "yy-mm-dd-hh-mm") & ".pdf")
       'above is code to rename file it it existes.
       
    
        'bellow is the code for saving the invoice in PDF 27/07/19
    
    Application.ActivePrinter = "doPDF v7 on DOP7:" 'Selects doPDF to genarate PDF file of invoice.
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "D:\2 hard drive\Lusa\Generated Invoices\INV" & Range("L17").Text & ".pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False
    If Application.Dialogs(xlDialogPrinterSetup).Show Then
    End If
                        
        'above is the code for saving the invoice in PDF 27/07/19
                   
    Sheets("Invoice").Unprotect Password:="test" 'Unprotects Saved Invoices sheet to record data from Invoice sheet.
                
        With ActiveSheet
            For i = 0 To 2
                .Range("T10").Value = D(i)
                .Range("T12").Value = E(i)
                .PrintOut Copies:=1, Collate:=True
            Next i
        End With
           
    Sheets("Saved Invoices").Unprotect Password:="test" 'Unprotects Saved Invoices sheet so that it can record data pulled from Invoice sheet.
   
    Dim Data(1 To 5) As Variant
    Dim DstRng As Range
    Dim RngEnd As Range
   
        Set DstRng = Worksheets("Saved Invoices").Range("A2:D2")
        Set RngEnd = DstRng.Parent.Cells(Rows.Count, DstRng.Column).End(xlUp)
        Set DstRng = IIf(RngEnd.Row < DstRng.Row, DstRng, RngEnd.Offset(1, 0).Resize(1, 5)) 'Changed the 4 to a 5... worked
       
        With Worksheets("Invoice")
            Data(1) = .Range("L17")  'Invoice number
            Data(2) = .Range("A17")  'Date
            Data(3) = .Range("AS1")  'Customer
            Data(4) = .Range("AZ73") 'Amount
            Data(5) = .Range("Z1")   'Not yet paid
        End With
       
        DstRng = Data
                   
        Sheets("Saved Invoices").Protect Password:="test" 'Protects Saved Invoices sheet so that data is not erased.
        
        Range( _
        "AS1:BH1,W17:AJ17,AX17:BH17,I20:AD67,AJ20:AL67,AV20:BB67,G70:T70,AA70:AL70,G71:AL71,G74:P74,G75:P75,Z74:AL74,Z75:AL75,T10,T12" _
        ).Select
        Range("Z75").Activate
        Selection.ClearContents
        
        Range("A1:BY1").Select 'Selects cells at top to which zoom is based.
        ActiveWindow.Zoom = True
        Range("AS1").Select
        ActiveWindow.LargeScroll Down:=-5
 
        With Range("L17")
        .NumberFormat = "00000"
        '.Value = .Value + 1
        End With
  
        'Range("L17:V17").Select
        'ActiveCell.FormulaR1C1 = "=R[-16]C[9]"
        'Range("AS1").Select
 
        Sheets("Saved Invoices").Unprotect Password:="test"
        
        Sheets("Saved Invoices").Select

        ActiveSheet.Range("A1:D5000").RemoveDuplicates Columns:=1, Header:=xlYes
        ActiveWorkbook.Worksheets("Saved Invoices").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Saved Invoices").Sort.SortFields.Add Key:=Range("A2:A5000") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Saved Invoices").Sort
            .SetRange Range("A2:D5000")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        
        Columns("B:B").Select
        Selection.NumberFormat = "dd/mm/yyyy;@"
        
        With Selection
        .HorizontalAlignment = xlRight
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        End With
        
 
        Sheets("Invoice").Select
        
        Range("L17").Select
        ActiveCell.FormulaR1C1 = "=R[-16]C[9]"
        'Range("AS1").Select
        
        Range("A17").Select
        ActiveCell.FormulaR1C1 = "=R[1]C"
        Range("AS1").Select
        
        Sheets("Saved Invoices").Protect Password:="test"
 
        Sheets("Invoice").Protect Password:="test" 'Protects the Invoice sheet.
   
        ActiveWorkbook.Save
 
    End If
    End If
    End If
    End If
    'End If
    'End If
       
End Sub

Thanks everyone.