Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Got a good one!!!!

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    97
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have an excel 2k workbook that is basically a front end to a access db (2k as well). In the workbook the user will select a value from a combo box, which will in turn populate certain cells in the workbook. Let's say the cells being populated are A1:A10. There are also two buttons, one to update the db with the values in A1:A10, another to restore the values from the db (simply in case the user made changes in the cells but decides he wants to go back to the db values). Here's what I want to do, maybe I'm getting to fancy. The user selects a value from the combo box, the cells A1:A10 are populated with values, with black text, aligned right. If the user changes that value, the text becomes red, aligned left. Am I dreaming?

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    97
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    So I guess I am dreaming, thanks anyway...

  3. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    You're not dreaming. It can be done. Just give people more time.

    I'll try to cook something up if I get some time. I rarely use Access, so you may have to adapt an Excel only solution to the Excel-Access link.

    This doesn't seem too difficult, only your short attention span may cause some problems.

    Bye,
    Jay

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    97
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry, wasn't trying to be impatient. I;m just so used to quick replies from everyone! Thanks in advance for your time!


  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    97
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I may be pushing my luck, but maybe someone who's on today can figure this one out... Thanks for considering...
    How long a minute is depends on which side of the bathroom door you are standing...

    Cheers

  6. #6
    New Member
    Join Date
    Apr 2002
    Location
    Lawrence, KS
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think this is actually very simple. You simply want the change in color to be temporary, right? The next time the data is loaded from the db you don't want anything to be red right?
    You should use the Worksheet_Change event. THis event takes a range as an argument and is called anytime the value in a cell is edited. THe edited cell will be the range passed as the target. Within this event routine you check the adress of the target to make sure it is one of the cells you wish to keep track of. If it is you set the target's font color to 255, or some other shade of red. WHen new data is loaded you will need to specifically reset the cell's font color. I'm sure the allignment is just as easy.

    [ This Message was edited by: dhoffman on 2002-04-24 11:45 ]

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Posts
    97
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That sounds about right, I'll give it a shot... Thanks





    How long a minute is depends on which side of the bathroom door you are standing...

    Cheers

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    97
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Please bare with me, but is this right?

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Range(Target).Font.Color = RGB(255, 0, 0)

    End Sub

    It must not be, since I get a run-time error: "Method 'Range' of object'_Global' failed."

    Enlighten me...



    How long a minute is depends on which side of the bathroom door you are standing...

    Cheers

  9. #9
    New Member
    Join Date
    Apr 2002
    Location
    Lawrence, KS
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    no, it appears you are using the code module linked to the workbook object. You want your code to go in the module linked to the worksheet itself. The name of the routine you will use is:
    Private Sub Worksheet_Change(ByVal Target As Range)

    code will be something like this:
    target.font.color = 255

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Posts
    97
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    No, that didn't seem to work. I don't know if I'm missing something, but that event does not get run when I type values into cells and press enter. I know this cause I set a breakpoint at the sub, and it didn't break.




    How long a minute is depends on which side of the bathroom door you are standing...

    Cheers

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •