Add Conditional Formatting to a User Form Command Button

[Denny57]

I have a series of Command Buttons in a User Form each of which will get different sets of information and poplate fields in the same User Form. I am wondering if there is a way to apply Conditional Formatting to each button based on the value of a specific cell in another worksheet.
The command Buttons are from the Developer Tool Box which appears with the User Form ( not Active x)

(If the cell in sheet 2 has a value > 0 then there is information to be access, other wise not.

Generic Requirement

User Form = frmInformation
Command Button 1
If value cell B2 in "sheet 2" > 0 then Command Button 1 BackColor = RGB(0,255,0)
Else Backcolour = RGB(255,0,0)

Command Button 2
If value cell D2 in sheet 2 > 0 then Command Button 2 Backcolour = RGB(0,255,0)
Else Backcolour = RGB(255,0,0)

Repeated for up to 20 Command Buttons

I assume that in each case I would need to activate Sheet2

 

[Denny57]

If the currency symbol is text then Val function will return 0
Try replacing it with CCur function & see if resolves the issue

Me.Controls("CommandButton" & i).BackColor = IIf(CCur(cell.Value) > 0, rgbGreen, rgbRed)

Dave

Dave, thank you for your help, however, as the changes I was trying to make to an existing workbook were only additional "Nice To Have" elements, I am in danger of breaking the code that currently works.

I have experienced the problems that changes to existing code can have, as once being an analyst supporing software changes through requirments and testing. Despite my getting on in years and not likely to need the VBA for employment, I am always up for a challenge and so I will consider undertaking some advanced VBA for Excel courses. Hopefully I can find some suitable online courses that will enable me to understand VBA logic and mimic various Excel formatting through VBA.

If you are aware of any such online courses or publications, I should be grateful to receive such information. Meanwhile just understanding how to get text to move from a TextBox to a worksheet in numeric, date, currency or other formats would be a welcome start.

Once again thank you for trying to provide a solution.

Regards

Dave

 

[dmt32]

If you are aware of any such online courses or publications, I should be grateful to receive such information.

Most answers can be found in the VBA helpfile but for a self learning course, Just Google VBA online courses & see which one looks like it will help you.

Meanwhile just understanding how to get text to move from a TextBox to a worksheet in numeric, date, currency or other formats would be a welcome start.

You need to use an appropriate type conversion function: Type conversion functions (VBA)

Dave

 

[Denny57]

I have decided that I cannot give up on this and so tried the code where the cells in the range contained a value and not a VLOOKUP formula and it works perfectly.

The code only needs to work when the form is initialized and the formulas must remain in their cells after the values are obtained, as they will need to be used repeatedly and for subsequent calculations in other worksheets.
If necessary I can apply the values to designated Cells

 Dim rng As Range, cell As Range
    Dim i   As Long
  
    Set rng = ThisWorkbook.Worksheets("Bet Selections").Range("B4,B11")
    For Each cell In rng.Cells
        i = i + 1
        Me.Controls("CommandButton" & i).BackColor = _
        IIf(Val(cell.Value) > 0, rgbLime, rgbRed)
    Next cell

This code is part of Sub UserForm_Initialize()

The Formuls are =VLOOKUP("Variable text",Sheet2!$A$5:$AC$70,19,FALSE) Where "Variable text" is different for each cell.
The Range is ThisWorksheets.Sheets("Sheet3").Range("B4,B11,B18,B26,K4,K15,K26,T4,T11,T26,AC4,AC15,AC26,AL4,AL15,AL26,AU4,AU15,AU26)
If necessary the resulting values can be written to ("B7,B14,B21,B29,K7,K18,K29,T7,T18,T29,AC7,AC18,AC29,AL7,AL18,AL29,AU7,AU18,AU29)

If someone could please provide me with the necessary additional script so that the above code can calculate the value of each formula and use the value without overwriting the formulas I would be extremely grateful.