Change color of CommandButtons in a Loop in UserForm

B

BluEEyE86

New Member
Joined
May 25, 2021
Messages
34
Office Version
  1. 365
Platform
  1. Windows
Hello,

I'd like to make this kind of template in USerForm

Skills.PNG


I'm writing code to change CommandButton color based on excel cell. CommandButton color will be the same like excell's cell color. I', able to do it one by one as below just for few buttons with number 1:

VBA Code: 
If ThisWorkbook.Worksheets("Skill_matrix_laboratorium").Cells(17, 4).Interior.Color = RGB(255, 255, 0) Then 'BHP
               
                    CommandButton4.BackColor = RGB(255, 255, 0)
                                
                Else: CommandButton4.BackColor = RGB(255, 255, 255)
            
            End If
    
            If ThisWorkbook.Worksheets("Skill_matrix_laboratorium").Cells(17, 7).Interior.Color = RGB(255, 255, 0) Then 'PPOŻ
               
                    CommandButton8.BackColor = RGB(255, 255, 0)
                                
                Else: CommandButton8.BackColor = RGB(255, 255, 255)
            
            End If

           If ThisWorkbook.Worksheets("Skill_matrix_laboratorium").Cells(17, 10).Interior.Color = RGB(255, 255, 0) Then
               
                   CommandButton12.BackColor = RGB(255, 255, 0)
                                
                Else: CommandButton12.BackColor = RGB(255, 255, 255)
            
           End If

I'm tryinf to do it in a loop but still have a problems like Buttons aren't marked or even UserForm isn't show.

VBA Code: 
For i = 1 To 72 Step 3
                    If ThisWorkbook.Worksheets("Skill_matrix_laboratorium").Cells(17, i).Interior.Color = RGB(255, 255, 0) Then
                        Select Case i
                            Case i = 4
                                ommandButton4.BackColor = RGB(255, 255, 0)
                            Case i = 7
                                CommandButton8.BackColor = RGB(255, 255, 0)
                            Case i = 10
                                CommandButton12.BackColor = RGB(255, 255, 0)
                        End Select
                    End If
                Next i

Can someone help me how to do it in a loop ?
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Welcome to the forum. :)

Where is your code located? In the userform?
 
Upvote 0
Hi,

make below change in your code:

VBA Code: 
Dim i As Integer
    ThisWorkbook.Worksheets("Skill_matrix_laboratorium").Cells(17, 1).Interior.Color = RGB(255, 255, 0)
    For i = 1 To 72 Step 3
            If ThisWorkbook.Worksheets("Skill_matrix_laboratorium").Cells(17, i).Interior.Color = RGB(255, 255, 0) Then
                        Select Case i
                            Case 4
                                CommandButton4.BackColor = RGB(255, 255, 0)
                            Case 7
                                CommandButton8.BackColor = RGB(255, 255, 0)
                            Case 10
                                CommandButton12.BackColor = RGB(255, 255, 0)
                        End Select
            End If
    Next i
 
Upvote 0
RoryA said:
Welcome to the forum. :)

Where is your code located? In the userform?
Click to expand...
Hello,

Yes, code is located in useform & needs to initilized when new window appear.

I'd like to do this loop as simple as possible as I need to take into consideration more people :)
 
Upvote 0
Saurabhj said:
Hi,

make below change in your code:

VBA Code: 
Dim i As Integer
    ThisWorkbook.Worksheets("Skill_matrix_laboratorium").Cells(17, 1).Interior.Color = RGB(255, 255, 0)
    For i = 1 To 72 Step 3
            If ThisWorkbook.Worksheets("Skill_matrix_laboratorium").Cells(17, i).Interior.Color = RGB(255, 255, 0) Then
                        Select Case i
                            Case 4
                                CommandButton4.BackColor = RGB(255, 255, 0)
                            Case 7
                                CommandButton8.BackColor = RGB(255, 255, 0)
                            Case 10
                                CommandButton12.BackColor = RGB(255, 255, 0)
                        End Select
            End If
    Next i
Click to expand...
I will check your proposal. Is there any method to make all command buttons in a loop ? I'm asking becasue just for level 1, I have 22 buttons.
 
Upvote 0
If it's in the form, then you could use something like this (I adjusted the loop to start at 4 rather than 1, since you don't seem to use the 1):

VBA Code: 
For i = 4 To 72 Step 3
    Dim theColour as long
    If ThisWorkbook.Worksheets("Skill_matrix_laboratorium").Cells(17, i).Interior.Color = RGB(255, 255, 0) Then
        theColour = RGB(255, 255, 0)
    Else
        theColour = RGB(255,255,255)
    End If
    Me.Controls("CommandButton" & i).BackColor = theColour
Next i
 
Upvote 0
Solution
RoryA said:
If it's in the form, then you could use something like this (I adjusted the loop to start at 4 rather than 1, since you don't seem to use the 1):

VBA Code: 
For i = 4 To 72 Step 3
    Dim theColour as long
    If ThisWorkbook.Worksheets("Skill_matrix_laboratorium").Cells(17, i).Interior.Color = RGB(255, 255, 0) Then
        theColour = RGB(255, 255, 0)
    Else
        theColour = RGB(255,255,255)
    End If
    Me.Controls("CommandButton" & i).BackColor = theColour
Next i
Click to expand...
Thank you, Your code work as I expected.

I have modified the code as below but I don't know why Buttons aren't marked. Everything was OK until I decalrated R1 & R2. R1 & R2 is a row number in excel file related to different person.

VBA Code: 
Private Sub UserForm_Initialize()

Dim i As Byte
Dim j As Byte

Dim R1 As Byte
Dim R2 As Byte

Label1 = ViewOP2.name

    If ViewOP2.name = "me" Then

        R1 = 17
        R2 = 18
        
    ElseIf ViewOP2.name = "me1" Then
    
        R1 = 20
        R2 = 21
    
    ElseIf ViewOP2.name = "me2" Then
    
        R1 = 23
        R2 = 24
        
    ElseIf ViewOP2.name = "me3" Then
    
        R1 = 26
        R2 = 27
        
    ElseIf ViewOP2.name = "me4" Then
    
        R1 = 29
        R2 = 30
        
    ElseIf ViewOP2.name = "me5" Then
    
        R1 = 32
        R2 = 33
        
'poziom 1 i 3 w petli
                
                For j = R1 To R2
                
                    For i = 4 To 72 Step 3
                        
                        Dim theColor As Long
                        Dim Yellow As Long
                        Dim Green As Long
                        
                            If j = R1 Then
                            
                                theColor = RGB(255, 255, 0)
                            
                                                      
                                    If ThisWorkbook.Worksheets("Skill_matrix_laboratorium").Cells(j, i).Interior.Color = theColor Then
                                        
                                        Yellow = RGB(255, 255, 0)
                                
                                    Else
                                        
                                        Yellow = RGB(255, 255, 255)
                                    
                                    End If
                              
                              ElseIf j = R2 Then
                            
                                theColor = RGB(0, 176, 80)
                                    
                                    If ThisWorkbook.Worksheets("Skill_matrix_laboratorium").Cells(j, i).Interior.Color = Color Then
                                        
                                        Green = RGB(0, 176, 80)
                                
                                    Else
                                        
                                        Green = RGB(255, 255, 255)
                                    
                                    End If
                                
                            End If
                                    
                                    If j = R1 Then
                                
                                        Me.Controls("CommandButton" & i).BackColor = Yellow
                                        
                                    ElseIf j = R2 Then
                                        
                                        Me.Controls("CommandButton" & i + 2).BackColor = Green
                                    
                                    End If
                        
                    Next i
                
                Next j
        
    End If

End Sub

Skills.PNG
 
Upvote 0
Green won't work because you're comparing the cell colour to a variable called color, not thecolor, and that has no value.
 
Upvote 0
RoryA said:
Green won't work because you're comparing the cell colour to a variable called color, not thecolor, and that has no value.
Click to expand...
Thanks for quick feedback. I made already changes as you suggested but this is still not the case.

VBA Code: 
Private Sub UserForm_Initialize()

Dim i As Byte
Dim j As Byte

Dim R1 As Byte
Dim R2 As Byte

Label38.BackColor = RGB(255, 255, 0)

Label47.BackColor = RGB(255, 255, 0)
Label46.BackColor = RGB(247, 150, 70)

Label52.BackColor = RGB(255, 255, 0)
Label51.BackColor = RGB(247, 150, 70)
Label49.BackColor = RGB(0, 176, 80)

Label57.BackColor = RGB(255, 255, 0)
Label54.BackColor = RGB(247, 150, 70)
Label56.BackColor = RGB(0, 176, 80)
Label55.BackColor = RGB(0, 176, 240)

Label1 = ViewOP2.name

    If ViewOP2.name = "me" Then

        R1 = 17
        R2 = 18
        
    ElseIf ViewOP2.name = "me1" Then
    
        R1 = 20
        R2 = 21
    
    ElseIf ViewOP2.name = "me2" Then
    
        R1 = 23
        R2 = 24
        
    ElseIf ViewOP2.name = "me3" Then
    
        R1 = 26
        R2 = 27
        
    ElseIf ViewOP2.name = "me4" Then
    
        R1 = 29
        R2 = 30
        
    ElseIf ViewOP2.name = "me5" Then
    
        R1 = 32
        R2 = 33
        
'poziom 1 i 3 w petli
                
                For j = R1 To R2
                
                    For i = 4 To 72 Step 3
                        
                        Dim theColor As Long
                        Dim Yellow As Long
                        Dim Green As Long
                        
                            If j = R1 Then
                            
                                theColor = RGB(255, 255, 0)
                            
                                                      
                                    If ThisWorkbook.Worksheets("Skill_matrix_laboratorium").Cells(j, i).Interior.Color = theColor Then
                                        
                                        Yellow = RGB(255, 255, 0)
                                
                                    Else
                                        
                                        Yellow = RGB(255, 255, 255)
                                    
                                    End If
                              
                              ElseIf j = R2 Then
                            
                                theColor = RGB(0, 176, 80)
                                    
                                    If ThisWorkbook.Worksheets("Skill_matrix_laboratorium").Cells(j, i).Interior.Color = theColor Then
                                        
                                        Green = RGB(0, 176, 80)
                                
                                    Else
                                        
                                        Green = RGB(255, 255, 255)
                                    
                                    End If
                                
                            End If
                                    
                                    If j = R1 Then
                                
                                        Me.Controls("CommandButton" & i).BackColor = Yellow
                                        
                                    ElseIf j = R2 Then
                                        
                                        Me.Controls("CommandButton" & i + 2).BackColor = Green
                                    
                                    End If
                        
                    Next i
                
                Next j
              
    End If

End Sub
 
Upvote 0
BluEEyE86 said:
Thanks for quick feedback. I made already changes as you suggested but this is still not the case.

VBA Code: 
Private Sub UserForm_Initialize()

Dim i As Byte
Dim j As Byte

Dim R1 As Byte
Dim R2 As Byte

Label38.BackColor = RGB(255, 255, 0)

Label47.BackColor = RGB(255, 255, 0)
Label46.BackColor = RGB(247, 150, 70)

Label52.BackColor = RGB(255, 255, 0)
Label51.BackColor = RGB(247, 150, 70)
Label49.BackColor = RGB(0, 176, 80)

Label57.BackColor = RGB(255, 255, 0)
Label54.BackColor = RGB(247, 150, 70)
Label56.BackColor = RGB(0, 176, 80)
Label55.BackColor = RGB(0, 176, 240)

Label1 = ViewOP2.name

    If ViewOP2.name = "me" Then

        R1 = 17
        R2 = 18
       
    ElseIf ViewOP2.name = "me1" Then
   
        R1 = 20
        R2 = 21
   
    ElseIf ViewOP2.name = "me2" Then
   
        R1 = 23
        R2 = 24
       
    ElseIf ViewOP2.name = "me3" Then
   
        R1 = 26
        R2 = 27
       
    ElseIf ViewOP2.name = "me4" Then
   
        R1 = 29
        R2 = 30
       
    ElseIf ViewOP2.name = "me5" Then
   
        R1 = 32
        R2 = 33
       
'poziom 1 i 3 w petli
               
                For j = R1 To R2
               
                    For i = 4 To 72 Step 3
                       
                        Dim theColor As Long
                        Dim Yellow As Long
                        Dim Green As Long
                       
                            If j = R1 Then
                           
                                theColor = RGB(255, 255, 0)
                           
                                                     
                                    If ThisWorkbook.Worksheets("Skill_matrix_laboratorium").Cells(j, i).Interior.Color = theColor Then
                                       
                                        Yellow = RGB(255, 255, 0)
                               
                                    Else
                                       
                                        Yellow = RGB(255, 255, 255)
                                   
                                    End If
                             
                              ElseIf j = R2 Then
                           
                                theColor = RGB(0, 176, 80)
                                   
                                    If ThisWorkbook.Worksheets("Skill_matrix_laboratorium").Cells(j, i).Interior.Color = theColor Then
                                       
                                        Green = RGB(0, 176, 80)
                               
                                    Else
                                       
                                        Green = RGB(255, 255, 255)
                                   
                                    End If
                               
                            End If
                                   
                                    If j = R1 Then
                               
                                        Me.Controls("CommandButton" & i).BackColor = Yellow
                                       
                                    ElseIf j = R2 Then
                                       
                                        Me.Controls("CommandButton" & i + 2).BackColor = Green
                                   
                                    End If
                       
                    Next i
               
                Next j
             
    End If

End Sub
Click to expand...
I already know where is the problem. All if's are only related to the person "me5" because code is under this person. This seems to be quite easy but not for me. I apologize to waste your time.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Userform inconsistent responses
Replies
1
Views
194
Candyman8019
C
S
  • Question Question
VBA help
Replies
1
Views
190
Micron
M
B
  • Solved
Need code that converts colors from the RGB code to the Natural Color System (NCS)
Replies
6
Views
225
PeteWright
PeteWright
K
  • Question Question
How to shorten vba code without any problems
Replies
1
Views
281
kamranyd
K
SeniorNewbie
  • Solved
Construct a line of code using values of an array
Replies
3
Views
234
Fluff
Fluff

Forum statistics

Threads
1,223,952
Messages
6,175,594
Members
452,656
Latest member
earth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top