VBA and the Color Palette

On 2003-01-16 11:48, craigg3 wrote:
I am using code to show the color palette, but cant seem to get the color to show up on the a cell, what code do I need to get the color from the palette to the cell.

If I am trying to apply color to range("A1") and I am using this code "Application.Dialogs(xlDialogColorPalette).Show" to show the palette how do I get it to put the color in the cell
Thanks

Click to expand...

craigg3, I don't know how you would select from the color pallete but by using the .Interior.ColorIndex you can set the cell color. The following code (from the Help files) will give you all the colors in Excel and their corresponding value which you can use.

Public Sub ColorTable()


For i = 1 To 56
Range("A" & i).Interior.ColorIndex = i
Range("B" & i).Value = i
Next i
End Sub

Howdy craigg3,

As I understand it, you can't return meaningful index numbers from the dialog, but have a play with the following:<pre>
Private Declare Function ChooseColor Lib "comdlg32.dll" Alias _
"ChooseColorA" (pChoosecolor As ChooseColor) As Long

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Type ChooseColor
lStructSize As Long
hwndOwner As Long
hInstance As Long
rgbResult As Long
lpCustColors As String
flags As Long
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Private Function ShowColor() As Long
Dim ChooseColorStructure As ChooseColor
Dim Custcolor(16) As Long
Dim lReturn As Long
ChooseColorStructure.lStructSize = Len(ChooseColorStructure)
ChooseColorStructure.hwndOwner = FindWindow("XLMAIN", _
Application.Caption)
ChooseColorStructure.hInstance = 0
ChooseColorStructure.lpCustColors = StrConv(CustomColors, _
vbUnicode)
ChooseColorStructure.flags = 0
If ChooseColor(ChooseColorStructure)<> 0 Then
ShowColor = ChooseColorStructure.rgbResult
CustomColors = StrConv(ChooseColorStructure.lpCustColors, _
vbFromUnicode)
Else
ShowColor = -1
End If
End Function

Sub ColorTime()
Selection.Interior.Color = ShowColor
End Sub</pre>

From here. Utilizes the Windows palette. Hope this helps.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2003-01-16 18:52

Hey Nate that's very neat and I never knock back code that has been offered as it often fits some other situation, but wouldn't it be easier to give the user a selected Formatting Toolbar.

EDit: I wish I knew how to do it.



_________________<font size=+1.<fontcolor="Blue">Sam</font size=+1.</fontcolor="Blue">
Why is it that the nuttiest people define reality?
This message was edited by SamS on 2003-01-17 00:17

Howdy Sam, thanks.

On 2003-01-17 00:16, SamS wrote:
Hey Nate that's very neat and I never knock back code that has been offered as it often fits some other situation,

Click to expand...

It seems to fit the exact quandary raised by the OP... Am I missing something here?

but wouldn't it be easier to give the user a selected Formatting Toolbar.

Click to expand...

I never know how to respond to this when it comes to pasting code into a module and clicking the play button or hitting f5. Creating a cold fission solution is difficult, running a vba procedure is always easy in my opinion.

EDit: I wish I knew how to do it.

Click to expand...

Are you asking how to reinvent your very own color palette? Why would you want to?

Have a good one.

Nate, I guess I was just wondering what use the OP really had in mind.

Hi thought ill toss this old chestnut in for fun

Jack

Code:

Sub Colours__()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual 'pre XL97 xlManual
Dim i As Long
Dim str0 As String, str As String
For i = 0 To 56
Cells(i + 1, 1).Interior.ColorIndex = i
Cells(i + 1, 1).Value = "[Color " & i & "]"
Cells(i + 1, 2).Font.ColorIndex = i
Cells(i + 1, 2).Value = "[Color " & i & "]"
str0 = Right("000000" & Hex(Cells(i + 1, 1).Interior.Color), 6)
'Excel shows nibbles in reverse order so make it as RGB
str = Right(str0, 2) & Mid(str0, 3, 2) & Left(str0, 2)
'generating 2 columns in the HTML table
Cells(i + 1, 3) = "#" & str & "#" & str & ""
Cells(i + 1, 4).Formula = "=Hex2dec(""" & Right(str0, 2) & """)"
Cells(i + 1, 5).Formula = "=Hex2dec(""" & Mid(str0, 3, 2) & """)"
Cells(i + 1, 6).Formula = "=Hex2dec(""" & Left(str0, 2) & """)"
Cells(i + 1, 7) = "[Color " & i & ")"
Next i
done:
Application.Calculation = xlCalculationAutomatic 'pre XL97 xlAutomatic
Application.ScreenUpdating = True
End Sub

Hi Nate,

Nice

Tried Your above sample and got an error however
I added following line in the declaration:

Dim CustomColors As Long

and now it running.

Warm regards,
Dennis

On 2003-01-19 18:18, Jack in the UK wrote:
Hi thought ill toss this old chestnut in for fun

Jack

Click to expand...

Jack, I've just replaced my tiny little piece of code with yours, cheers

Hi Jack

Nice one - thanks altough I was forced to replace the Hex2Dec with my local formula :wink:

Kind regards,
Dennis