Tools/Macro/Visual Basic Editor

I'm new to this but I have finally hacked out something that does what I want 
by using a basic template from another user. My code is at the bottom of the 
message.

This changes the fill of the identified shapes based upon some value.

The first line of the code: "Private Sub Worksheet_Change(ByVal Target As 
Range)"
was included in the template. I don't know if this is just a title, whether 
it has some specific syntax or format or what. 

I also cannot find a list of Me.Shapes. "Face" is a smiley face that was 
included in the template and "Auto Shape 2" is a shape that I added using the 
draw tool. It seems that somewhere in Excel I should be able to find a list 
of these shapes and that there should be a way to rename the AutoShapes to 
something more meaningful like you would with a range name.

Any help will be appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
    Me.Shapes("Face").Select
    With Range("FaceInd")
        If .Value <= 3 Then
            Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)
        ElseIf .Value > 3 And .Value <= 6 Then
            Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 254, 0)
        Else
            Selection.ShapeRange.Fill.ForeColor.RGB = RGB(88, 146, 0)
        End If

        .Select
    End With

    Me.Shapes("AutoShape 2").Select
        With Range("AutoShape2ID")
        If .Value <= 3 Then
            Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)
        ElseIf .Value > 3 And .Value <= 6 Then
            Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 254, 0)
        Else
            Selection.ShapeRange.Fill.ForeColor.RGB = RGB(88, 146, 0)
        End If

        .Select
    End With

End Sub
0
Utf
9/9/2007 6:54:01 PM
access 16762 articles. 3 followers. Follow

2 Replies
926 Views

Similar Articles

[PageSpeed] 5

looks like you're using Excel here, not MS Access. suggest you post your
question to an Excel newsgroup.


"AL2000" <AL2000@discussions.microsoft.com> wrote in message
news:8AFDB03D-286D-43AF-AB22-8700EFBE48F7@microsoft.com...
> I'm new to this but I have finally hacked out something that does what I
want
> by using a basic template from another user. My code is at the bottom of
the
> message.
>
> This changes the fill of the identified shapes based upon some value.
>
> The first line of the code: "Private Sub Worksheet_Change(ByVal Target As
> Range)"
> was included in the template. I don't know if this is just a title,
whether
> it has some specific syntax or format or what.
>
> I also cannot find a list of Me.Shapes. "Face" is a smiley face that was
> included in the template and "Auto Shape 2" is a shape that I added using
the
> draw tool. It seems that somewhere in Excel I should be able to find a
list
> of these shapes and that there should be a way to rename the AutoShapes to
> something more meaningful like you would with a range name.
>
> Any help will be appreciated.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>     Me.Shapes("Face").Select
>     With Range("FaceInd")
>         If .Value <= 3 Then
>             Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)
>         ElseIf .Value > 3 And .Value <= 6 Then
>             Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 254, 0)
>         Else
>             Selection.ShapeRange.Fill.ForeColor.RGB = RGB(88, 146, 0)
>         End If
>
>         .Select
>     End With
>
>     Me.Shapes("AutoShape 2").Select
>         With Range("AutoShape2ID")
>         If .Value <= 3 Then
>             Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)
>         ElseIf .Value > 3 And .Value <= 6 Then
>             Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 254, 0)
>         Else
>             Selection.ShapeRange.Fill.ForeColor.RGB = RGB(88, 146, 0)
>         End If
>
>         .Select
>     End With
>
> End Sub


0
tina
9/9/2007 7:34:39 PM
Oops. Thanks. 

"tina" wrote:

> looks like you're using Excel here, not MS Access. suggest you post your
> question to an Excel newsgroup.
> 
> 
> "AL2000" <AL2000@discussions.microsoft.com> wrote in message
> news:8AFDB03D-286D-43AF-AB22-8700EFBE48F7@microsoft.com...
> > I'm new to this but I have finally hacked out something that does what I
> want
> > by using a basic template from another user. My code is at the bottom of
> the
> > message.
> >
> > This changes the fill of the identified shapes based upon some value.
> >
> > The first line of the code: "Private Sub Worksheet_Change(ByVal Target As
> > Range)"
> > was included in the template. I don't know if this is just a title,
> whether
> > it has some specific syntax or format or what.
> >
> > I also cannot find a list of Me.Shapes. "Face" is a smiley face that was
> > included in the template and "Auto Shape 2" is a shape that I added using
> the
> > draw tool. It seems that somewhere in Excel I should be able to find a
> list
> > of these shapes and that there should be a way to rename the AutoShapes to
> > something more meaningful like you would with a range name.
> >
> > Any help will be appreciated.
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> >     Me.Shapes("Face").Select
> >     With Range("FaceInd")
> >         If .Value <= 3 Then
> >             Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)
> >         ElseIf .Value > 3 And .Value <= 6 Then
> >             Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 254, 0)
> >         Else
> >             Selection.ShapeRange.Fill.ForeColor.RGB = RGB(88, 146, 0)
> >         End If
> >
> >         .Select
> >     End With
> >
> >     Me.Shapes("AutoShape 2").Select
> >         With Range("AutoShape2ID")
> >         If .Value <= 3 Then
> >             Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)
> >         ElseIf .Value > 3 And .Value <= 6 Then
> >             Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 254, 0)
> >         Else
> >             Selection.ShapeRange.Fill.ForeColor.RGB = RGB(88, 146, 0)
> >         End If
> >
> >         .Select
> >     End With
> >
> > End Sub
> 
> 
> 
0
Utf
9/9/2007 7:44:01 PM
Reply:

Similar Artilces:

importing text file into excel file using macro
Hi, How to import text file into excel file using Macro? Can this proces run on form menu button? Please help and give me some ideas of doin it. I have attached a text file for your reference. Thanks, An Attachment filename: rdata.txt Download attachment: http://www.excelforum.com/attachment.php?postid=66130 -- Message posted from http://www.ExcelForum.com >Hi, >How to import text file into excel file using Macro? Can this process >run on form menu button? Please help and give me some ideas of doing >it. I have attached a text file for your r...

Macro that deletes values with condition
Hi, I need a macro that deletes duplicates of numbers that appear an odd number of times and that deletes duplicates and the value duplicated an even number of times. Example: Original data A 1 2 3 1 2 1 2 3 4 Result: A 1 2 4 Values 1 and 2 must remain and only delete duplicates because they appear an odd number of times (3), 3 must be deleted because it appears an even number of times (2) and 4 appears because it has no duplicates. Hope this can be done! Thank you so much Hi Insert a heading in row 1 and try this macro: Sub aaa() Dim f As ...

CONDITION IN MACRO #2
This is an update to an earlier question I posed. I am using Access 2002. I'm trying to use a Condition in a Macro to determine whether or not to SetValue. It appears that the Condition will not allow me to use a "wildcard" (asterisk). The Field I'm checking is filled with entries like CRUZ SA or CRUZ SB or CRUZ SC. I simply want to set the Condition to look for CRUZ*...meaning any entry that begins with CRUZ. The Condition I set up is [CLASS]="CRUZ*". It doesn't work. But, when I set the Condition to [CLASS]="CRUZ SA" it works. Access Help seem...

Outlook 2003, Word 2007 email editor
Is it possible to use Word 2007 and the email editor in Outlook 2003? I don't think it is, however there may be a download that someone knows about. Thanls, Steve no. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Subscribe to Exchange Messaging ...

Record Macro Relative does not work?
Can someone help me with this macro. I want to go to any cell of my choice and press f2 to edit, then go to the fifth character of that cell, then select the next 6 characters and change them to a smaller font size (8), then "enter". I tried to make this macro using the record macro function, but it did not work how I want it to. It ends up pasting the contents of the cell which I used to record the macro initially. Can this kind of macro be done? To get a relative recorded macro, make sure the stopr recording toolbar is visible, it has 2 icons, one is to s...

Yet another Lotus macro question
I have for the most part converted to Excel but have a simple macro that on the surface cannot be converted. The Lotus macro used a database, template and list of branch names. The macro itself started with the name of the first branch, copied it to the template, sucked in the data for that branch and then printed the report. Then it went down to next name on the list and repeated the process until it reached the bottom of the list. In Excel it seems to indicate I have to repeat the steps for each and every branch on the list and not loop back through a 8 line macro that refers to the ...

Macros Disabled #2
Hello, I have a user running Excel 2000 on a Win 2000 sp3 machine. She has a file with several macros. The file is a form with data fields for names, numbers, etc. As soon as she enters any data and clicks the tab button to go to the next field, she gets an error message that the Macros have been disabled and the Tab key won't go to the next field. She emailed me the file and when I open it on my machine (same setup), I first get asked if I want to enable the macros. I say Yes - and everything works fine. I emailed the file back to the user, but it's still broken on her...

Macro HELP!
I am trying to build a macro that will check for the day of the week in cell A1. "Mon", "Tue etc.., if true, it will copy the contents of another cell and paste to another cell. Hope this is discriptive enough. Help the newbie ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements Cycle, I think you should ask a more specific question. Maybe this is a first step...

Some macro help with formatting object please
I have a macro that turns on the calculate command function while the user works in it. I was asked if I could have the color of the text box that launches the macro turned RED when the calculate feature has been turned off. Is that possible? TIA! activesheet.textbox1.backcolor=&h0000ff -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Alan" <me@here.now> wrote in message news:oYeGc.8684$WM5.492396@news20.bellglobal.com... > I have a macro that turns on the calculate command fu...

Excel 2007 Macro: Open a file from any folder
Hello. How do you code a macro so that you can pop up a file dialog screen to open a worksheet from any folder of your choice? Thanks in advance. Same as always: Dim myName As String myName = Application.GetOpenFilename If myName = False Then MsgBox "You pressed cancel" Exit Sub End If Workbooks.Open myName HTH, Bernie MS Excel MVP "C C" <someone@atsbcglobal.net> wrote in message news:eS8FI0UeIHA.3940@TK2MSFTNGP05.phx.gbl... > Hello. How do you code a macro so that you can pop up a file dialog screen to open a worksheet > from any folder of y...

macro #5
i want to make a macro record saving a file but it has to be differnet name file on the sheet. example if i change the name on the cell the save got to be the same name too when i click on save the macro would be asign to a picture. try activeworkbook.SaveAs Filename:=range("a1") "guest" <anonymous@discussions.microsoft.com> wrote in message news:047f01c39a78$28ce7d40$a301280a@phx.gbl... > i want to make a macro record saving a file but it has to > be differnet name file on the sheet. example if i change > the name on the cell the save got to be the s...

recovered messages folders again and again and cant visualize
Today windows mail "crashed" with no obvious reason. After that a "recovered messages" folder started to appear, so moved my "recovered" out the recovered folder to it's original position. I was almost glad at that point, but now on a closer look what I have is: My folders have the full amount of my messages, and my messages keep the subject and file size, but I can't visualize any content (just an error message instead saying message can't show), I'm surprised because those are not 0 kb files... Besides, every lauch I get the recove...

Excel macro for copying range to another worksheet
On a monthly basis, I would like to copy the completed range (varies from month to month) of a database (Sheet1) to a master list (Sheet3). Once the data has been copied I intend to manually delete the entries of Sheet1 and start anew for the new month =96 for eventual transfer to Sheet3. The idea is to copy each month=92s data at the bottom of the previous months=92 (Sheet3). I followed Excel=92s record macro command but the macro I ended up with is not capable of placing the new data at the bottom of the existing one; it simply keeps overwriting the previous entry. Unfortunately, I don=92t...

Outlook takes long time in new message with word 2003 as editor
Good Day Urgently need help in troubleshooting a problem which is spread among many clients in which Outlook takes long time when you create or reply first message after starting outlook , our clients are windows XP with SP3 and the Microsoft office is office 2003 with SP3 , My machine suffers from this error although i have patched it to the latest updates from Microsoft update , I have Mcafee Viruscan Enterprise 8.5.0i and i have disabled access protection when outlook startes but it did not solve the problem Apprecite quick advice and any troubleshooting tips Ahmedg You mentioned e...

Enable Macro Message
How can you disable the message upon opening a file without setting the security to lowest? I do not want to give the user options to disable or enable the macro. Is that possible? Thanks. This is a user's security setting--not a developer's security setting. If you could change it, it wouldn't be of much use to the user, huh? Rook wrote: > > How can you disable the message upon opening a file without setting the > security to lowest? I do not want to give the user options to disable or > enable the macro. > > Is that possible? > > Thanks. -- Da...

Creating Event procedures from a macro
Hi, I'm using Jon Peltier's workaround (to Excel's inability to create hyperlinks to chart sheets) to create a table of contents. The following event procedure is placed in the code module for the "contents" sheet. It activates the chart sheet when the user selects the linked cell: ' Thank you Jon! Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("B2")) Is Nothing Then Charts("Chart1").Activate End If End Sub This works fine, except that I need to create it on the fly from within a ma...

Named Ranges
Hi! I created several worksheets that contained huge lists, so I created a dynamic named range. Now that the name has been inserted in my array formulas, my Macro runs very slowly. The Macro unprotects one of my sheets, sorts the data in a table, hides the blank rows and the protects the sheet again. There are no related named range formulas in that worksheet, so I'm not sure why it would be affected. The Macro ran quickly when I had a defined range of data. Would giving a named range like 'Date' and having text in my workbook with 'Date' create conflicts? Could this...

Menu in Word behaving oddly; items (like the entire contents of the Tools menu) disappearing...
Hey, I'd be oh-so-grateful if anyone has any idea how I could address this problem. I'm quite mystified. I'm running MS Word for X on my Mac (OS 10.4.3), and lately some odd things have begun happening. At first I was just working around them, but I've decided live my computer life too much that way (ignoring problems and hoping they go away), so thought I should look into this before it gets out of control. Items have been disappearing from the Word menu bar. That is, for instance, after I had used the "Symbols" option under the "Insert" menu one day, it di...

Macro won't delete? Any ideas?
Hi, I inserted an Excel 5.0 dialog worksheet into my spreadsheet. Excel seemed to insert also a macro, which I then located (Tools -> Macro -> Macros, then deleted all the macros in the document). I also deleted the worksheet. However, since then, Excel asks me every time I open it if I would like to enable macros. This is very annoying seeing as I thought I deleted them. Can anyone direct me where I might find the remaining macro? thanks. G Gabbon You have to delete the module that held the code too. Check here http://www.nickhodge.co.uk/tipstrick.htm#macrowarningremoval ...

Microsoft Excel 2007 to Mac Excel 2008 Issue regarding Macros
I have created worksheets on my PC using MS Excel 2007. I transfered them to a Mac user and when open the excel worksheet on his computer it tells me &quot;Excel 2008 for Mac can't run the Macros&quot; <br><br>Can someone maybe please help me to a way around this ???? Is there a version that does support macros ? Or...can the macros be re-created &amp; run ? > Is there a version that does support macros ? Or...can the macros be re-created &amp; run ? <br><br>Mac Excel 2004. ...

Excel macro #12
I have recorded two macroes, put them together and added if-then-else statement, but there is something wrong. It does not work. Both macroes work fine separately. I try to filter data from a base workbook to an active workbook and make it run automatically every time when the file is opened. Excel should check if a base file is open and if so run the filter routine. If that file is not open it should be opened and run filter. Sub macroname() If IsFileOpen("\\serverName\path\fileName.xls") Then Workbooks("fileName.xls").Sheets("Sheet1").Range("rangeName1...

Options okn Tools Menu
I have a user who is using Excel 97. When he goes to the menu bar and selects Tools then Options, the Options chioce is there but when you clck on it it does nothing. Has anyone had this issue before and if so how can I get his Options functionality back? Excel and Office has been uninstalled and reinstalled several times. Maybe this is it: XL97: Page Fault or Violation When You Click Options on the Tools Menu http://support.microsoft.com/default.aspx?scid=kb;en-us;108032 sounds like it describes your Tools|Options problem (it says it was corrected in xl97 SR2). Ron Davidson wrote:...

Small macro problem
Dim rng As Range Set rng = Range("g17").End(xlDown).Resize(1, 1) rng.AutoFill Destination:=rng.Resize(rng.Columns.Count, 2) What I want the above to do, is to go to the last item in Column G (say G5) and copy it to the cell below (G6), but this fills both G6 and H7. I feel sure it is very easy to resolve but I seem to have tried all permutations without success. Please advise where I am going wrong. Bob Switch your Row/Column values in .Resize(): Dim rng As Range Set rng = Range("G17").End(xlDown) rng.AutoFill Destination:=rng.Resize(...

Custom format macro?
Can I write a macro to format a number? I have a bunch of cells containing time intervals stored as floating point numbers in units of days. These are not dates, but are stored in the same format. Some examples: 1 1 day 2.5 2.5 days or 2 days, 12 hours 0.125 3 hours 0.020833 30 minutes and so on. These values are calculated. I would like to display them as nnn.n u where "nnn.n" is a floating point number formatted to 1 decimal place and "u" is a character indicating the units (Y=years, D=days, H=hours, M=minutes, S=seconds). The ...

Password Recovery Tool ? ? ?
Hi All Ive got a Workbook that I need to recover the password for the Sheet Protection....Any Suggestion on the Best password recovery tool to use, would be greatly appreciated ! ! ! Thanks Dodge Dodge, Make the sheet you want to remove the protection from the active sheet and then run this subroutine. It will "come" back with teh message that an password is found. Click OK and the protection from the sheet will be removed. Sub PasswordBreaker_Sheet() 'Author unknown 'Breaks worksheet password protection. Dim i As Integer, j As Integer, k As Integer ...