Change Button Color Upon Click

[carddard]

Hi guys,

I was wondering if it is possible to change the color of a command button after it's been clicked?

This is to allow the user to identify which buttons have already been clicked.

Thanks!

 

[micro-blades]

It's just an example and will need to be edited to fit the rest of you code, it's not intended to work as is.

How are you calling the inputbox? How do you currently handle the user clicking cancel? How do you handle the user clicking ok?

If you can post some of your code it would be easier to tell you exactly what you need to do.

 

[carddard]

No, I mean I've tried putting the code into my own one before you suggested it, and it didn't work.

Here's the command button:

Private Sub CommandButton3_Click()
Dim HeldsecDir As String

'Change heldsec directory here:
HeldsecDir = "C:\Documents and Settings\500722\Desktop\Macros\Convert Heldsec\Daily Heldsec Reports"

Application.StatusBar = "Running macro, please wait..."

    If Hour(Time) < 17 Then
        Call heldsec11(HeldsecDir) 'Module1
    Else
        Call heldsec5(HeldsecDir) 'Module1
    End If
    
    'Change button to yellow
    With CommandButton3
        .Caption = "Done"
        .BackColor = &HFFFF&
    End With

Application.StatusBar = "Completed!"

End Sub

Here's the sub it runs:

Sub heldsec11(directory As String)
Dim thiswb, HeldsecName, OpenHeldsec, HeldsecWSname, UpdateConfo
On Error GoTo Err_Handler
thiswb = ActiveWorkbook.Name

UpdateConfo = MsgBox("Have you updated all Dates?", vbYesNo)
    
    If UpdateConfo = vbNo Then Exit Sub
    
'Open heldsec
OpenHeldsec = InputBox("Press OK to open today's Held Securities Report dated " & Date, "Opening File at " & Time, _
directory & "\Heldsec" & Format(Date, "yymmdd") & "(File1).xls")
If OpenHeldsec = "" Then Exit Sub
Application.ScreenUpdating = False
Workbooks.Open Filename:=OpenHeldsec
HeldsecName = ActiveWorkbook.Name
HeldsecWSname = Left(HeldsecName, 13)

'Copy heldsec to worksheet "Heldsec"
With Workbooks(HeldsecName)
    .Sheets(HeldsecWSname).AutoFilterMode = False
    .Sheets(HeldsecWSname).Range("A:O").Copy Workbooks(thiswb).Sheets("Heldsec").Range("A1")
    .Close
End With

Workbooks(thiswb).Activate
Sheets("Heldsec").Select

'Formula - Vlookup TYPE ('Heldsec' column C) to tag security with asset class.
Range("P2:P" & Range("A" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(RC[-13],tagarray,2,FALSE)"

'Forumula - Get absolute variance using (K-I/K)*100. Display 0 if there is an error.
Range("Q2:Q" & Range("A" & Rows.Count).End(xlUp).Row).FormulaR1C1 = _
"=IF(ISERROR(ABS((RC[-6]-RC[-8])/RC[-6])*100),0,(ABS((RC[-6]-RC[-8])/RC[-6])*100))"

Call GroupAssetClasses(9.99999, 9.99999, 4.99999, 9.99999, 9.99999) 'Module2
Call updateDates 'Module3

Application.ScreenUpdating = True
MsgBox "Data has been grouped."

Err_Handler:
Select Case Err.Number
Case Is = 1004
MsgBox "File cannot be found in the directory.", vbExclamation, "Error"
End Select

End Sub

 

[micro-blades]

That's much better, thank you. Your code as is does not handle a click of the cancel button but rather only if the user enters nothing "".

Remove what I have highlighted in red, add what have highlighted in green. On this line: "With Sheet1.CommandButton3" change the sheet name if needed.

By moving the the commandbutton color code to the sub and after "If OpenHeldsec = False" the color will not be changed unless the user enters a value and click ok.

Private Sub CommandButton3_Click()
Dim HeldsecDir As String

'Change heldsec directory here:
HeldsecDir = "C:\Documents and Settings\500722\Desktop\Macros\Convert Heldsec\Daily Heldsec Reports"

Application.StatusBar = "Running macro, please wait..."

    If Hour(Time) < 17 Then
        Call heldsec11(HeldsecDir) 'Module1
    Else
        Call heldsec5(HeldsecDir) 'Module1
    End If
    
    'Change button to yellow
    With CommandButton3
        .Caption = "Done"
        .BackColor = &HFFFF&
    End With

Application.StatusBar = "Completed!"

End Sub

Sub heldsec11(directory As String)
Dim thiswb, HeldsecName, OpenHeldsec, HeldsecWSname, UpdateConfo
On Error GoTo Err_Handler
thiswb = ActiveWorkbook.Name

UpdateConfo = MsgBox("Have you updated all Dates?", vbYesNo)
    
    If UpdateConfo = vbNo Then Exit Sub
    
'Open heldsec
OpenHeldsec = InputBox("Press OK to open today's Held Securities Report dated " & Date, "Opening File at " & Time, _
directory & "\Heldsec" & Format(Date, "yymmdd") & "(File1).xls")
If OpenHeldsec = "" Then Exit Sub

If OpenHeldsec = False Then Exit Sub
    'Change button to yellow
    With Sheet1.CommandButton3
        .Caption = "Done"
        .BackColor = &HFFFF&
    End With

Application.ScreenUpdating = False
Workbooks.Open Filename:=OpenHeldsec
HeldsecName = ActiveWorkbook.Name
HeldsecWSname = Left(HeldsecName, 13)

'Copy heldsec to worksheet "Heldsec"
With Workbooks(HeldsecName)
    .Sheets(HeldsecWSname).AutoFilterMode = False
    .Sheets(HeldsecWSname).Range("A:O").Copy Workbooks(thiswb).Sheets("Heldsec").Range("A1")
    .Close
End With

Workbooks(thiswb).Activate
Sheets("Heldsec").Select

'Formula - Vlookup TYPE ('Heldsec' column C) to tag security with asset class.
Range("P2:P" & Range("A" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(RC[-13],tagarray,2,FALSE)"

'Forumula - Get absolute variance using (K-I/K)*100. Display 0 if there is an error.
Range("Q2:Q" & Range("A" & Rows.Count).End(xlUp).Row).FormulaR1C1 = _
"=IF(ISERROR(ABS((RC[-6]-RC[-8])/RC[-6])*100),0,(ABS((RC[-6]-RC[-8])/RC[-6])*100))"

Call GroupAssetClasses(9.99999, 9.99999, 4.99999, 9.99999, 9.99999) 'Module2
Call updateDates 'Module3

Application.ScreenUpdating = True
MsgBox "Data has been grouped."

Err_Handler:
Select Case Err.Number
Case Is = 1004
MsgBox "File cannot be found in the directory.", vbExclamation, "Error"
End Select

End Sub

[/QUOTE]

 

[carddard]

Hey micro blades, there's another problem.
If the error trap is called, meaning if there is an error, the button changes colour nonetheless.

 

[carddard]

Ah. I figured out.
I'll just place the colour change code at the very end of the code.

 

[micro-blades]

You got it!