You cannot use true conditional formatting for anything but cells.
Is the content of B2 and D2 values or formulas?
You will have to write event handlers in Sheet 2 to monitor changes in those cells, and that code will update the color of the buttons in the UserForm. There are two issues:
1. If the cells contain values that the user types in, you can use the Worksheet_Change event. If they contain formulas, you will have to use the Worksheet_Calculate event and check those cells every time there any recalculation of the sheet. This could have a slight impact on your user experience depending on how often there are recalculations.
2. The UserForm will also have to have code to set the color of these buttons in the UserForm_Initialize event so it will show the correct colors each time the form is newly instantiated.
Why do you say "up to"? Isn't there a definite number of buttons?
The code has to be written for each button and cell combination. If there is a pattern (like B2, D2, F2.... and Button1, Button2, Button3, etc.), it can be done with a loop. Otherwise you will have to have 20 cases. How are your buttons named, and what cells determine their colors?
You should never activate a sheet to read data from it or write data to it in VBA.
I can help with actual code once you provide the above details.
So, as once being an analyst looking for solutions to client requirements, I perhaps have a different understanding / expectation of coding, as I only ever requested solutions from the software developers. I am now what might be known as a "Silver Surfer" who keeps seeking solutions to improbable requirements!!!
I have developed this project over a period of time and it is basically a way of calculating the current status, returns and potential returns of different betting combinations.
Whilst I expect that a progammer might look at the archiecture and formulas which my file uses might look "clunky" (it includes a number worksheets which reference each other) , I am happy that whilst not being a programmer, that these actually work very well.
As a further challenge (and to keep my brain active), I have considered the use of 2 User Forms in the workbook. The first I use to input and update selection and result information
(completed and functioning perfectly) and the second I use to display individual bet combination information. This has separate Command Buttons for each bet combination o be displayed (and it is to these that I wish to apply conditional formatting).
I currently have a table on Sheet 1, which looks at the value of a cell in Sheet3 which has been dynamically assigned to a bet combination and which is used to set the Conditional Formatting in the table in Sheet 1. I now wish to apply similar formatting to the individual Command Buttons in the second User Form and remove the current table.
The command Buttons in the second User Form works as expected, however, I am hoping that I can apply conditional formatting to each Command Button, by way of a BackColor to indicate active bet combinations. ( I have considered using a single Command Button which looks up a bet combination in a comboBox / TextBox, but could not get this to work. Instead I added a Command Button for each Bet Combination, hence why there are 19 Command Buttons.
From your response I have determined
1) The the lookup detail is a Value and will be different for each Command Button and is the resule of a VLOOKUP formula
2) Number of Command Buttons = 19
3)
The code has to be written for each button and cell combination. =
Yes
Each Command Button (there are 19) has a different cmdxxxxx name
Each Command Button looks at the formula result of a dynamically assigned cell in Sheet3, different for each bet combination, to determine
a) If the assigned cell value ="0" (Returns Conditional Formatting = BackColor RGB(255,0,0) {Red}
b) otherwise Conditional Formatting = BackColor RGB(0,255,0) {Green}
eg Bet Combination S1 lookup = Sheet3 Cell k2
eg Bet Combination D2 lookup = Sheet3 Cell H7
Sorry if this is little longwinded but hopefully this explains what I am hoping to, achieve.
Current code for the simplest Command Button ( only 1 selection) however, I would expect that a separate command Button to update all 19 Command Buttons might be a better option if not too long for VBA.
The idea of a" Refresh" Command Button seems the most appealing option.
I would just need the correct syntax
perhaps something along the lines of
VBA Code:
Private Sub cmd_Refresh
'Refreshes Active Bet Command Buttons
If (Sheets("Bet Selections").Range("K4").Value = 0 Then
cmdSingle1.BackColor = RGB(255, 0, 0) 'Red!
Else
cmdSingle2.BackColor = RGB(255, 255, 0) 'Green!
If (Sheets("BetSelections").Range("D16").Value = 0 Then
cmdSingle2.BackColor = RGB(255, 0, 0) 'Red!
Else
cmdSingle1.BackColor = RGB(255, 255, 0) 'Green!
End Sub
Hoping you can help
