Posts by mjschukas

?does the named range have to be in the same sheet or on any sheet (and have you done integration with access--my employees come from a table in access--and i want to automate the link (both ways) better than i currently have...

thanks again...!

Quote

Originally posted by Derk
I suggest you copy this question and paste it in as a new question, since it's on a different topic (and start a new thread for your other questins when the topic is different). That way you have a better chance of getting the best answer. (For example, I'm a beginner on manipulating toolbars, but others on the forum are expert and should be the ones to answer this question).) Specify what version of Excel you are using, as the answer may depend on the version.

thank you...!!!!

i have a number of questions, but maybe one at a time...

i create a customized toolbar and sometimes:

i want to install (so to speak...) on a person's computer (so that no matter what spreadsheet they bring up this toolbar is available...

other times, i want to customize a toolbar and only have it "load" when the person loads a specfic spreadsheet (on any computer as long as a person has the .xls)

thank you...!

Quote

Originally posted by Derk
Yes, never hesitate to ask a question. That's why this forum is here.

thank you...!

(may i ask you another question...?)

Quote

Originally posted by Derk
In the last line of your code that you posted, the .interior should be changed to .colorindex as in my code that you quoted.

we are getting better...:)

this works:

Dim isname
If Not Intersect(Target, Cells) Is Nothing Then
isname = Application.Match(Target, Range("ColorMap"), 0)
If IsNumeric(isname) Then _
Target.Interior.ColorIndex = _
Range("ColorMap").Cells(isname).Interior.ColorIndex
End If

and this doesn't yet...thank you...!

Dim isname
If Not Intersect(Target, Cells) Is Nothing Then
isname = Application.Match(Target, Range("ColorMapA"), 0)
If IsNumeric(isname) Then _
Target.Font.ColorIndex = _
Range("ColorMapA").Cells(isname).Font.Interior

Quote

Originally posted by Derk
To change the font color instead of teh cell color just replace the word Interior with the word Font. The lighter font colors, however, are hard to see. I don't know why the routine would lock up Excel. It's a very simple bit of code.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isname
If Not Intersect(Target, Cells) Is Nothing Then
    isname = Application.Match(Target, Range("ColorMap"), 0)
    If IsNumeric(isname) Then _
        Target.Font.ColorIndex = _
            Range("ColorMap").Cells(isname).Font.ColorIndex
End If
End Sub

Can you expain a bit more what you mean by
"the color doesn't change after entry (and enter)--this would be nice "

thank you...!

and a few notes...

the color doesn't change after entry (and enter)--this would be nice

and can i change the font color vs. the backcolor...

and this code has "locked up" my excel app. a number of times...what would you advise about running this app...?

thank you...!

Quote

Originally posted by Derk
Here is an alternative way to do it. Create a master list of names and color each name with the color you want. This lets you select the colors from the color pallet instead of trying to guess the correct number. Define this master list as, say, ColorMap. Then use the following event macro on your worksheet 9adjustingthe intersect range as desired).

Code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isname
If Not Intersect(Target, Cells) Is Nothing Then
    isname = Application.Match(Target, Range("ColorMap"), 0)
    If IsNumeric(isname) Then _
        Target.Interior.ColorIndex = _
            Range("ColorMap").Cells(isname).Interior.ColorIndex
End If
End Sub

<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"&gt;</SCRIPT&gt;<CENTER&gt;<TABLE cellSpacing=0 cellPadding=0 align=center&gt;<TBODY&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=5&gt;<TABLE width="100%" align=center border=0&gt;<TBODY&gt;<TR&gt;<TD align=left&gt;<FONT color=white&gt;Microsoft Excel - junk.xls</FONT&gt;</TD&gt;<TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right&gt;___Running: xl97 : OS = Windows NT 4 </FONT&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=5&gt;<TABLE width="100%" align=center border=0 VALIGN="MIDDLE"&gt;<TBODY&gt;<TR&gt;<TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption"&gt;(<U&gt;F</U&gt;)ile (<U&gt;E</U&gt;)dit (<U&gt;V</U&gt;)iew (<U&gt;I</U&gt;)nsert (<U&gt;O</U&gt;)ptions (<U&gt;T</U&gt;)ools (<U&gt;D</U&gt;)ata (<U&gt;W</U&gt;)indow (<U&gt;H</U&gt;)elp <A onclick=show_popup(); href="#javascript:void(0)"&gt;(<U&gt;A</U&gt;)bout</A&gt;</TD&gt;<TD vAlign=center align=right&gt;<FORM name=formCb755237&gt;<INPUT onclick='window.clipboardData.setData("Text",document.formFb078704.sltNb935705.value);' type=button value="Copy Formula" name=btCb873980&gt;</FORM&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=5&gt;<TABLE border=0&gt;<TBODY&gt;<TR&gt;<FORM name=formFb078704&gt;<TD style="WIDTH: 60px" align=middle bgColor=white&gt;<SELECT onchange="document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value" name=sltNb935705&gt;<OPTION value="" selected&gt;C2</OPTION&gt;</SELECT&gt;</TD&gt;<TD align=right width="3%" bgColor=#d4d0c8&gt;<B>=</B&gt;</TD&gt;<TD align=left bgColor=white&gt;<INPUT size=80 value=Joe name=txbFb426622&gt;</TD&gt;</FORM&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<BR&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;A</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;B</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;C</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;D</CENTER&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;1</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;ColorMap</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: left"&gt;Helen</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;2</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left"&gt;Jim</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left"&gt;Joe</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;3</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: left"&gt;Mary</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: left"&gt;Mary</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;4</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffcc99; TEXT-ALIGN: left"&gt;Helen</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: left"&gt;Jim</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"&gt;&nbsp;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" colSpan=5&gt;<TABLE width="100%" align=left VALIGN="TOP"&gt;<TBODY&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; WIDTH: 120pt; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff" align=left&gt;<U&gt;Sheet3</U&gt;</TD&gt;<TD&gt;&nbsp;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;<BR&gt;<FONT color=#339966 size=1&gt;[HtmlMaker 2.41] </FONT&gt;<FONT color=#339966 size=1&gt;To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT&gt;<BR&gt;<FONT color=red size=1&gt;PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</FONT&gt;</CENTER&gt;

ok...but how do i set up the color coding for each employee...?

Quote

Originally posted by royUK
In this line
If Not Intersect(Target, Range("A1:A10")) is Nothing The
change the Range("A1:A10") to Cells to include the whole sheet. I don't think it will change the existing entries but if you put the code into a before)Click procedure and then click on each existing entry it should apply the format

ok...thank you and i'm ok with the case statement...

?how do i run this for a whole worksheet (i.e., all the center entries looking for names to change the font color)...?

thank you.

Quote

Originally posted by mjschukas
color coordinating employee names...

i would like to color (font) code my employees.

i.e., on a weekly work schedule each employee has their name and their color
e.g,
if mark then font = blue
if steve then font = purple
etc.

?what might an easy to do this (n.b., a color associated with an employee would be static week to week)

thank you...!

Display More

color coordinating employee names...

i would like to color (font) code my employees.

i.e., on a weekly work schedule each employee has their name and their color
e.g,
if mark then font = blue
if steve then font = purple
etc.

?what might an easy to do this (n.b., a color associated with an employee would be static week to week)

thank you...!

when i run the following code under a button on a sheet, it works just fine, but when i run the code from a customized icon on a toolbar, i get an error message...?

If Calendar1.Visible = True Then

Calendar1.Visible = False

Else

Calendar1.Visible = True

End If

thank you.

http://www.quickbooks.com/supp…w2002/premier/117503.html

Sample IIF files for individual transactions

i need to export some excel information to quickbooks...

(i have the record layouts from the quickbooks site, but i wouldn't mind some comments from somenone who has done this already...)

thanks...!

i need some help in creating a class in excel...

(this is new to me and i'd just like a simple example...)

thank you...!

i'm having trouble with time arithmetic...

i put the time in b2 and i want to add 1 hour to this in b3...

?why am i having troubles...?

thank you.

i would like to create a pop-up window in a worksheet.

n.b., i would like to list employees in the pop-up and be able to make a selection.