Hexidecimal to Interior Cell Color

[Sbaugh]

Reading through the Forum I have found some possible answers yet I still cannot get this to work.

I posted this question here already, but no replies:

http://www.ozgrid.com/forum/showthread.php?t=149311

I was hoping someone might be able to help mefigure out what the problem is.

I found this old thread: thread707-1325614: Excel - setting background color from RGB code and I think this will work if I can figure out how to make do what I need.

I just want to change a Combobox to a selected color per a table I have made that has the cooresponding hexidecimal next to the color. Then I use a Vlookup to display the Hexidecimal used for that selected color. I want that Hex to be used to create a color in a completely different cell in the table. Here is an image of what I have in my Excel sheet.

Thanks,

Scott Baugh, CSWP

http://www.scottjbaugh.com

 

[macropod]

Hi Scott,

If your ComboBox is from the Control Toolbox, you can use code like:

Private Sub ComboBox1_Change()
With Worksheets("Sheet1").OLEObjects("ComboBox1").Object
  .BackColor = Worksheets("Sheet1").Range("D8").Interior.Color
End With
End Sub

but you can't change the colour of a ComboBox from the Forms Toolbar.

For demonstration purposes, create a new workbook with a vba module. In that module put and run:

Sub CellColor()
Dim i As Integer
With Worksheets("Sheet1")
  For i = 1 To 56
    With .Cells(i, 1)
    .Value = i
    .Interior.ColorIndex = i
    End With
  Next
End With
End Sub

In the 'ThisWorkbook' module, put:

Private Sub Workbook_Open()
Worksheets("Sheet1").OLEObjects("ComboBox1").ListFillRange = "A1:A56"
End Sub

and, in the Shhet1 module, put:

Private Sub ComboBox1_Change()
With Worksheets("Sheet1").OLEObjects("ComboBox1").Object
  .BackColor = Worksheets("Sheet1").Cells(.Value, 1).Interior.Color
End With
End Sub

On sheet 1, insert an ActiveX Combo Box.

Save, close & re-open the workbook and you should be able to colour the combobox with whatever colour value you select.

Cheers
Paul Edstein
[MS MVP - Word]

 

[Sbaugh]

Sorry for the late reply, Holiday chaos and return to work chaos.

I think I put your code together like you listed above, but I am only getting the numeral "1" to appear in A1... Was that the intent of your code or am I missing something?

Thanks for you reply, I truly appreciate it!


Scott Baugh, CSWP

http://www.scottjbaugh.com

 

[macropod]

Hi Scott,

Correctly set up, your combo box colour should change.

Cheers
Paul Edstein
[MS MVP - Word]

 

[Sbaugh]

Thanks for your reply.. The code I ended up using was:

Private Sub ComboBox1_Change()

Dim i As Long
    
   With Me
        i = WorksheetFunction.Match(.ComboBox1.Value, Sheet2.Range("G1:G56"), 0)
        
        i = Sheet2.Cells(i, 1).Value
        
        .Range("AD32").Interior.ColorIndex = i
        .Range("AD30").Value = i
                
   End With
End Sub

What I originally wanted to do is make the Combobox select not only a color word, but the actual color all within the Combo Box, but nothing I found on that made since to me.

Now I am taking this entire Spread sheet and trying to turn it into a Userform so I can make it an external application to control a Part file inside of Solidworks. I did this many years ago, and i still have my example, but its still a little ways out of my knowledge anymore. Getting Old Sucks, can't remember everything I used to.

Thanks for your reply! I truly appreciate it.

Scott Baugh, CSWP

http://www.scottjbaugh.com