Color Picker in Excel

Hi All,

I will appreciate if someone can guide me to do a color picker using
VBA in excel. I need to display the 57 colors of the palette and get
the color index returned to put that value in a cell.
A simple box with colors to do this using userform on xl's inbuilt
dialog will be fine.

Looking forward to all your help !

Thanx and regds
Shuvro

0
shuvrobasu (10)
4/19/2006 7:39:44 AM
excel 39879 articles. 2 followers. Follow

10 Replies
522 Views

Similar Articles

[PageSpeed] 11

'-----------------------------�------------------------------�--------------
--
Function GetColorindex(Optional Text As Boolean = False) As Long
'-----------------------------�------------------------------�--------------
--
Dim rngCurr As Range

    Set rngCurr = Selection
    Application.ScreenUpdating = False
    Range("IV1").Select
    Application.Dialogs(xlDialogPatterns).Show
    GetColorindex = ActiveCell.Interior.ColorIndex
    If GetColorindex = xlColorIndexAutomatic And Not Text Then
        GetColorindex = xlColorIndexNone
    End If
    ActiveCell.Interior.ColorIndex = xlColorIndexAutomatic
    rngCurr.Select
    Set rngCurr = ActiveSheet.UsedRange
    Application.ScreenUpdating = True
End Function



-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Shuvro Basu" <shuvrobasu@gmail.com> wrote in message
news:1145432384.204311.86380@i39g2000cwa.googlegroups.com...
> Hi All,
>
> I will appreciate if someone can guide me to do a color picker using
> VBA in excel. I need to display the 57 colors of the palette and get
> the color index returned to put that value in a cell.
> A simple box with colors to do this using userform on xl's inbuilt
> dialog will be fine.
>
> Looking forward to all your help !
>
> Thanx and regds
> Shuvro
>


0
bob.phillips1 (6510)
4/19/2006 8:20:28 AM
Hi Bob,

Thanks for your response. However I wanted to know whether we can use
this in a userform instead of a worksheet. I'm trying to select a color
(using the inbuilt dialog for palette) however not able to get the
index value returned. Hence my question.

Regds

0
shuvrobasu (10)
4/19/2006 8:52:36 AM
You can use that in a userform. Just create a coomandbutton and do this in
its click event and see

    Msgbox GetColorindex()

-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Shuvro Basu" <shuvrobasu@gmail.com> wrote in message
news:1145436756.228970.70030@i40g2000cwc.googlegroups.com...
> Hi Bob,
>
> Thanks for your response. However I wanted to know whether we can use
> this in a userform instead of a worksheet. I'm trying to select a color
> (using the inbuilt dialog for palette) however not able to get the
> index value returned. Hence my question.
>
> Regds
>


0
bob.phillips1 (6510)
4/19/2006 9:54:41 AM
Hi Bob,

Thank you once again for your response. Ok. Here is the issue. What I
want to do is change the color of a selected chart series (one data
point which could be a column, line or bar) to the color I have
selected in the dialog with the color I have selected (from the
function).

Hope I have been more clear this time to explain my question

Regds

0
shuvrobasu (10)
4/19/2006 12:56:20 PM
I would suggest you record a macro that changes the data point in the chart
series, and then change the hardcoded colorindex value with the value
returned from that function.

-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Shuvro Basu" <shuvrobasu@gmail.com> wrote in message
news:1145451380.215554.320600@i40g2000cwc.googlegroups.com...
> Hi Bob,
>
> Thank you once again for your response. Ok. Here is the issue. What I
> want to do is change the color of a selected chart series (one data
> point which could be a column, line or bar) to the color I have
> selected in the dialog with the color I have selected (from the
> function).
>
> Hope I have been more clear this time to explain my question
>
> Regds
>


0
bob.phillips1 (6510)
4/19/2006 2:46:52 PM
Hi Bob,

Here is the code that I'm using.. and it is not working :-(.

I use this code in a commandbutton click event:

On Error Resume Next
op = GetColorindex()

With Selection.Border
        .Weight = xlHairline
        .LineStyle = xlNone
    End With
    Selection.Shadow = False
    Selection.InvertIfNegative = False
    Selection.Fill.OneColorGradient Style:=msoGradientVertical,
Variant:=4, _
        Degree:=0.231372549019608

    ActiveChart.SeriesCollection.Select
    With Selection
        .Fill.Visible = True
        .Fill.ForeColor.SchemeColor = op
    End With

Please let me know what I need to do to get this corrected. Any help
will be highly appreciated.

regds

0
shuvrobasu (10)
4/20/2006 4:24:45 AM
Addendum:

Further, when I select the column and try to run the macro I get an
error in the following line :
Set rngCurr = Selection in the ColorIndex() function. Though the data
points are selected, it seems that it is not treated as a valid
selection. Since I'm not using this on a cell in a worksheet rather on
a chart, I had removed the reference to the sheet in line :
Range("IV1").Select and put ActiveSheet.ActiveChart.Select and tried..
obviously this also failed. Moreover it is not easy to determine which
data point (i.e. series in the chart) will be selected by the user,
hence I cannot use :  ActiveChart.SeriesCollection(1).Select.

Seems that I have landed in more trouble than I expected !

Note: (All code reference above refer to the ColorIndex() function)

Regds

0
shuvrobasu (10)
4/20/2006 4:32:39 AM
Maybe try this version

'-----------------------------�------------------------------�--------------
Function GetColorindex(Optional Text As Boolean = False) As Long
'-----------------------------�------------------------------�--------------
Dim rngCurr As Range
Dim oThis As Object

    Application.ScreenUpdating = False
    Set oThis = ActiveSheet
    Worksheets(1).Activate
    Set rngCurr = Selection
    Range("IV1").Select
    Application.Dialogs(xlDialogPatterns).Show
    GetColorindex = ActiveCell.Interior.ColorIndex
    If GetColorindex = xlColorIndexAutomatic And Not Text Then
        GetColorindex = xlColorIndexNone
    End If
    ActiveCell.Interior.ColorIndex = xlColorIndexAutomatic
    rngCurr.Select
    Set rngCurr = ActiveSheet.UsedRange
    oThis.Activate
    Application.ScreenUpdating = True
End Function


-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Shuvro Basu" <shuvrobasu@gmail.com> wrote in message
news:1145507559.097329.147170@i39g2000cwa.googlegroups.com...
> Addendum:
>
> Further, when I select the column and try to run the macro I get an
> error in the following line :
> Set rngCurr = Selection in the ColorIndex() function. Though the data
> points are selected, it seems that it is not treated as a valid
> selection. Since I'm not using this on a cell in a worksheet rather on
> a chart, I had removed the reference to the sheet in line :
> Range("IV1").Select and put ActiveSheet.ActiveChart.Select and tried..
> obviously this also failed. Moreover it is not easy to determine which
> data point (i.e. series in the chart) will be selected by the user,
> hence I cannot use :  ActiveChart.SeriesCollection(1).Select.
>
> Seems that I have landed in more trouble than I expected !
>
> Note: (All code reference above refer to the ColorIndex() function)
>
> Regds
>


0
bob.phillips1 (6510)
4/20/2006 4:30:24 PM
Hi Bob,

Cool ! This is working.. Really appreciate your help from the bottom of
my heart...

With best wishes and regards
Shuvro

0
shuvrobasu (10)
4/21/2006 10:21:29 AM
Shame about the background when it throws up the dialog, but I'm glad it is
working for you.

-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Shuvro Basu" <shuvrobasu@gmail.com> wrote in message
news:1145614889.593669.31180@i39g2000cwa.googlegroups.com...
> Hi Bob,
>
> Cool ! This is working.. Really appreciate your help from the bottom of
> my heart...
>
> With best wishes and regards
> Shuvro
>


0
bob.phillips1 (6510)
4/21/2006 11:46:20 AM
Reply:

Similar Artilces:

Macro for transferring data from excel to word
Hi, I have an excel file and a word file. I want a macro in excel which would open the existing word file in system, then go to the specified location in the word file, delete the existing picture from word file, copy a range/picture from excel file and then paste that onto the word file as picture enhanced format or bitmap format. Which code should i use for the same? Regards, Ajay Varshney Hi, It's generally advisable to "pull" data rather than to "push" it. So this means you should code this in Word VBA. I suggest you post the question in the Word Newsgrou...

Working with Both Excel 2003 and 2007
I'm in the middle of transitioning from 2003 to 2007 (half company on each version) How do you set it so that Excel 2007 in the primary excel...ie when openning a file from it automatically defaults to 2007 insted of 2003 Can this be done? Thanks -- Helping Is always a good thing Hi, I have E2003, E2007 & E2010 on the same machine and as I've added new versions the latest version has become the 'default' version. Are you saying this isn't happening? -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduc...

Can Excel startup Word?
Since Excel and Word seem to be part of the same MS-Office software set, I assume that Excel can easily be used to open Word??? Just wondering if I can automate Word from the Excel application in any way. thank u Yo can do it this way: Sub test() Dim oWord As Object Dim oDoc As Object Set oWord = CreateObject("Word.Application") oWord.Visible = True End Sub -- Regards! Stefi „Robert Crandal” ezt írta: > Since Excel and Word seem to be part of the same > MS-Office software set, I assume that Excel c...

Excel Speadsheets
We just installed Office 2007 on our pc's. In Excel 2003 if someone was in the spread sheet it would bring up a "read only" box listing the name of the person that was in the spreadsheet. 2007 just says "another user" is in it. Is there a way to change that to show the name of the user? The users names already show on thier own pc's in Office. ...

Excel 2000: Combo box cell link protection question
If the cell to which a combo box "cell link" refers, is protected then Excel generates an error when the combo box value is changed. Is there a way to to have the particular cell protected and yet be able to change the combo box value? Bertus Have the unprotected cell in a hidden row or column -- Message posted from http://www.ExcelForum.com ...

copy chart & colors change
If I make a copy of a chart (line type with 20 sets of data), it will look just like the original. If I change the source data in the copy all 20 lines change color. Why is this and how can I prevent it? Yes, that can be very frustrating. Unfortunately, XL essentially 'redraws' the entire chart. I don't know of any way to retain original information other than to have a macro redo the format the way you want after XL finishes doing its stuff. Of course, you can always do the same by hand and that is something I do if the amount of work is not very much or it's somet...

How do I create a form in Excel that I can send to be filled out?
I want to create a form in Excel that I can send out so people can fill it out electronically and then send it back to me. I need lines in the form and when I add lines I have to widen the column and then it messes up the previous wording. I know it can be done but not sure if I have to protect it, create a template (how do I do that), or lock some of the cells. Help! For lines have you considered simply bottom borders? i.e. wording in A1 bottom borders on B1:E1 Or lines drawn from the Drawing Toolbar. No need to widen any columns. Save the workbook and send it out...

Increasing file size from excel 2003 to excel 2007
Some users of ours have noticed that saving Excel 2003 files in Excel 2007 caused them to increase in size ans also get corrupted. Has any one else noticed this? ...

Input boxes in excel and MS Query
I'm using MS Excel to display the results of a SQL Server database, using the GET EXTERNAL DATA wizard, which then takes you to Microsoft Query so you can set up the SQL coding. I was having difficulty writing the SQL using MS Query, so I set up the same scenario in MS Access to write a query, then just pasted the query into MS Query. Unfortunately the code does not work and I get a "Invalid column name What is the start date" error What I'm actually trying to do is get two input boxes to appear each time the spreadsheet is opened. The user types the FROM and TO dates in...

Ignore the 'file in use message' when opening Excel
Hi all, I am scheduling an Excel workbook using the Scheduler Tool of Windows. It just calls an Excel workbook, which opens, does some autorun processing and closes again. This works well, except when I have excel already open. In that case another instance of Excel is opened (by the scheduler) and I get the "File in Use" popup stateing that my PERSONAL.XLS is locked for editing. And gives me a chance to cancel (button). A logical thing but unhandy in this context. Is there a way to ignore this message or to add an extra parameter to the call of the worksheet to preven...

excel macro om selectie op blad1 te kopieren en te plakken op een variabele locatie
Hallo Ik ben al een poosje aan het stoeien om het volgende voor elkaar te krijgen: ik heb een barcodescanner, deze genereert een txt bestand. ik heb een macro die dit bestand importeert, en de aantallen per barcode in range b3:b52 neerzet. als ik de macro nu de volgende dag opnieuw uitvoer, ben ik deze data dus weer kwijt. Hiertoe wil ik dus nadat de macro uitgevoerd is, range b3:b52 kopieren en plakken op blad2. Op blad 2 staan in rij 1 alle data voor dit jaar. Het doel van de macro is de range te plakken op blad2 in rij 3:52. de kolom is afhankelijk van de datum. Ik hoop dat ik een klein...

Excel 2007 Macro/VB Question DDE Question
Hi, So I have a column of cells that are the combination of a other cells in the row and a result of a few "if functions." The value comes out exactly as planned however in order for the data to be sent to the DDE server I need to press F2, go the beginning of the cell to insert an "=" sign and then press enter. I would like a macro to do what I just mentioned and then go down to the cell in the column. I used to write code using C++, but I'm a little rusty and unfamiliar with VB. Below is the formula and then the result of the cell. Any information or s...

Excel 2007 with delete deny can't save
Hi I have a big problem with Excel 2007. I have a shared folder on our file server where users can modify the excel files within, but can't delete them, I have set delete deny on permission. With Excel XP all was working fine, with Excel 2007 users can't save the file that have modified, if they try have an error message. The permission on the folder are: modify allow Read & execute allow List Folder Contents allow Read allow Write allow and on advanced settings Delete...

hyperlink excel 2007 backslash Internet Explorer 7
In excel I can create a hyperlink without any problem, byt when I pick when I wish to open that web page a '/' gets appended to the URL ex: in excel http://someplace:7080/xx When it gets to IE it is http://someplace:7080/xx/ the / has been added to the end, there the url will not work. This is all internal to access programs that are IE based. ...

mailmerge in excel
using excel to set up forms since layout is so easy. now that xp office has so easy mail merge, is it possible to personalize or populate excel worksheets with other excel data? lookups will work, but mail merge would seem to be easier, by selecting the addressees and let 'r rip thanks Mail merge is in MS Word and is not going to populate an Excel worksheet. You could use a macro in Excel to use a template and to copy to selected cells, or to replace cell content based on value form another worksheet. Part of whether this is feasible or what you want might be are you planning on keep...

How can put numering or pullets in one cell in excel sheet
How can put numering or pullets in one cell in excel sheet I'm guessing you want to have a bullet list of items contained in only one cell that implements word wrap.... Something like this..all in cell A1: •Item one •Item two •Item three If that's correct, try something like this: 1_Select the cell 2_Hold down [Alt]...type 0149...Release [Alt] (that puts the bullet point "•" in the cell) 3_Type the first list item 4_Hold down [Alt] and Press [Enter] (That will start a new line in the same cell) 5_Repeat steps 2, 3, and 4 for each list item 6_When done Press [Ent...

How do I delete a "function" from a cell in MS Excel?
When I type a number into a cell, and move on, the number inside the cell turns to "###". I just want the number to appear in the cell without any functions. Try increaseing the width of the cell. "Chris" wrote: > When I type a number into a cell, and move on, the number inside the cell > turns to "###". I just want the number to appear in the cell without any > functions. Or using a smaller font. Chris wrote: > > When I type a number into a cell, and move on, the number inside the cell > turns to "###". ...

Why can't I open any email jokes that friends send me with excel
I get email from friends that send me jokes to open with micro excel and I can't open them why? 1. Do you have Microsoft Excel installed on your computer? 2. What is the extension of the attachments with the jokes? Is it somejoke.XLS ? I never heard of Excel being a joke transporter but who knows? Gord Dibben MS Excel MVP On Sun, 27 May 2007 13:54:00 -0700, Leona <Leona@discussions.microsoft.com> wrote: >I get email from friends that send me jokes to open with micro excel and I >can't open them why? Its because Excel is only for serious work except, of c...

excel template wizard #5
When opening an xls file the warning "cannot open template wizard" comes up. it has been years since I played with the template wizard (useless to me) so now how can I get rid of the warning? Thanks Henry CCBW ...

Convert Works file to Excel file
Is there a simple conversion package to convert a Microsoft Works file, with extension .xlr, to an Excel file? Is there a download? Note I am running Excel 2002. ...

How to import flow charts made in excel to visio as editable text #2
I have created flow charts using drawing objects (rectangles, circles etc) in excel. Now I want to import these diagrams in visio, such that I am able to edit the text entered in the rectangles etc while preparing the charts in excel. I have tried it by copying the individual rectangles / circles in excel and pasting them in visio. But they appear in visio as uneditable objects (similar to jpg image of text which cannot be edited as text). Kindly advise on how to import the excel diagrams as editable visio diagrams. See reply in visio.general group -- Best regards John John Gold...

unable to read excel 97 files
My laptop has the XP operating system and the oil companies we work for use the old Excel 97 spread sheets. When I try to load the files I get an "unreadable format xls." message. Is there anything I can do short of purchasing a newer version of Office? What is your version.______? Version of the files ________? If you have xl97 OR later, you should be able to open and read the xl97 files. If you have a later version and the files were saved as .xls files, you still should be able to. -- Don Guillett SalesAid Software donaldb@281.com "Sue" <anonymous@discussions...

I am entering date in Excel when I hit enter a formula appears?
I have cleared everything is the cell, etc. Nothing has worked. Please advise. Are you including the leading = character when you enter your date? You should not. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Sun Devil" <Sun Devil@discussions.microsoft.com> wrote in message news:2F76A03C-4668-4E16-9153-FAD29402FE70@microsoft.com... >I have cleared everything is the cell, etc. Nothing has worked. > > Please advise. Tools>Options>Transition. Uncheck the Transition Formula Entry box. Uncheck the o...

Color Banding
I have read the material at the Chip Pearson's website on color banding in a worksheet to emulate greenbar. Works great, but I dont believe I understand what precisly the Conditional formatting formula is doing. I have used both =MOD(ROW()-Rw,N*2)+1<=N Rw is the starting row number to be banded, N is the number of rows to be banded. and =mod(ROW(),2) Can someone please take a few minutes to explain. Thank you. Hi Paul MOD(ROW(),2) returns the rest of the divison of the row number and two. e.g. row 1: MOD(1,2) = 1 row 2: MOD(2,2) = 0 row 3: MOD(3,2) = 1 row 4: MOD(4,2) = 0 Excel eva...

copy/paste excel charts as pictures
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC When I attempt to paste charts from Excel into Word using the 'copy picture' command, it tells me 'not enough memory'. So I upgraded from 512 Ram to 1.5 Ram, but still the same message. What's the problem? ...