How to save the colored button state in form after exit Excel vba?

[Masta]

I almost finish my little project for my job to automatize processes but i encounter 1 problem. I need help with coding in VBA, I don't know how to save the state of the CommandButton1 and the rest of the CommandButtons the buttons were pressed and then they changed color and that change is saved the next time I open that file so I can see that change I made. So far, I have coded the color change I wanted, but when a refresh of the form is done or Excel is exited, that change is reset and returns to its original state as if there was no change. I try with case statement but i have no luck so far. Thank you.
Example:

Private Sub CommandButton1_Click()
CommandButton1.BackColor = RGB(0, 200, 150) 'Green
Worksheets("Odgovori").Range("H6").Value = "e-Mail"
End Sub

Private Sub CommandButton2_Click()
CommandButton2.BackColor = RGB(255, 255, 0) 'Yellow
Worksheets("Odgovori").Range("H6").Value = "Adresa"
End Sub

 

[My Aswer Is This]

When you create the Button why not set the color the way you want in the properties window?

 

[Masta]

When you create the Button why not set the color the way you want in the properties window?

That change happens when is button clicked. Colored buttons is clicked buttons.

 

[Colo]

Unfortunately, once the Userform is unloaded, Excel cannot keep the changing state. Need to put it back programmatically as follows.

Private Sub UserForm_Initialize()
    If Worksheets("Odgovori").Range("H6").Value <> "" Then
        CommandButton1.BackColor = RGB(0, 200, 150)    'Green
    End If

    If Worksheets("Odgovori").Range("H6").Value <> "" Then
        CommandButton2.BackColor = RGB(255, 255, 0)    'Yellow
    End If
End Sub

 

[My Aswer Is This]

So you do not want to set the button to always be green. But only when you click the button but then want it to stay that way? Is that correct?

 

[Masta]

Unfortunately, once the Userform is unloaded, Excel cannot keep the changing state. Need to put it back programmatically as follows.

Private Sub UserForm_Initialize()
    If Worksheets("Odgovori").Range("H6").Value <> "" Then
        CommandButton1.BackColor = RGB(0, 200, 150)    'Green
    End If

    If Worksheets("Odgovori").Range("H6").Value <> "" Then
        CommandButton2.BackColor = RGB(255, 255, 0)    'Yellow
    End If
End Sub

I will try this one ty.

 

[Masta]

So you do not want to set the button to always be green. But only when you click the button but then want it to stay that way? Is that correct?

Yup

 

[My Aswer Is This]

Yup

The initialize suggest will work but that would set the button color to a specific color every time you opened the Userform.

Another way would be to use Hide instead of Unload when you close the userform.
But that would not work when you close the workbook.