Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Sep 2003
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Anchoring Command Code to a Cell (Excel 2000 SR-1)

    Hello again all,

    I'm probably trying to do something impossible again, but here goes....

    I have a command button in my spreadsheet. In the code associated with the button, a cell (or cell range) is referenced in the worksheet, to which an operation is performed when the button is clicked. I'm not having any problems with the function of my button when clicked. The problem occurs if/when I insert a new row in the worksheet, somewhere above the cell range referenced in the button code. The button code doesn't recognized that everything has shifted down by one row number. Is there anything I can do to overcome this?

    As always, your assistance is greatly appreciated. [img]/forums/images/smilies/smile.gif[/img]

    Regards,

    DG

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Anchoring Command Code to a Cell (Excel 2000 SR-1)

    If you can describe how you would recognize that everything had shifted down by one cell, then you can probably write code to recognize the same thing. If you can't describe how you would recognize this, then you probably will have to change your worksheet design so that the code can tell. You would have to provide a lot more information, and possibly a sample worksheet and your code before anyone can give a specific answer.
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Anchoring Command Code to a Cell (Excel 2000 SR-1)

    I assume that the code is written to reference a specific cell - something like:

    If Range("$A$15").value > 1000 then
    Range("$A$15").font.bold = true
    End If

    or somesuch

    The problem is that when you insert rows above row 15 the VBA code does not change - it still looks at A15 for a value and to perfrom the operation. The solution is to define a name for that range in the excel s/sheet - range names are attached to a particular cell or range, and "move" with the cell(s) when rows or columns are inserted or deleted. If you create a named range of "TestRange" for cell A15 you can rewrite the above snippet as:

    If Range("Testrange").value > 1000 then
    Range("testrange").font.bold = true
    End If

    <font color=red>OR</font color=red>

    If [Testrange].value > 1000 then
    [testrange].font.bold = true
    End If

    Note that the default property for a range object is "value" so you could just test if the range is > 1000, without specifying that the range.value > 1000. As well, you can see that VBA is not case-sensitive when using range names.

  4. #4
    New Lounger
    Join Date
    Sep 2003
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Anchoring Command Code to a Cell (Excel 2000 SR-1)

    Thanks, Dean! I haven't yet tried this out, as I ran across another problem. It seems like when I insert a new row, it also messes up my existing conditional formatting, so I'm trying to work through that first. I'll let you know soon.

    Thanks,

    DG

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Anchoring Command Code to a Cell (Excel 2000 S

    Conditional Formatting defaults to putting in Cell refernces as fixed ($A$15). If you want them to flex, you'll have to take out the $ signs when you first enter the Conditional Formats. HTH
    Gre

  6. #6
    New Lounger
    Join Date
    Sep 2003
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Anchoring Command Code to a Cell (Excel 2000 S

    Yep, that worked. Unfortunately, I have quite a long list of cells that are conditionally formatted. I had used the $ to allow me to copy and paste the format from the first cell to the rest. Now it would appear that I can no longer do that, unless I want to go back through and manually delete all the $ signs. That would be almost as much fun as manually entering all of the conditional formats! <img src=/S/doh.gif border=0 alt=doh width=15 height=15> Either that or look at a redesign of my spreadsheet.

    Thanks for the advice, tho'. I appreciate it.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Anchoring Command Code to a Cell (Excel 2000 S

    Are the conditional formats really different from cell to cell, or do they only differ in the address referenced? If the latter, you can select an area and set conditional formatting for the first cell in the area. When you click OK, Excel will set conditional formatting for the other cells of the area with formulas automatically adjusted.

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Anchoring Command Code to a Cell (Excel 2000 S

    <!profile=HansV>HansV<!/profile>'s nimble fingers got there first!

    The alternative I use is the Format Painter (the Brush) in the Standard Toolbar. Choose your cell without the $ signs. Double click on the Format Painter and then drop the cursor into as many cells as you like wherever in the Worksheet it may suit you.

    This works not only for Conditional Formatting but for any other kind of formatting you choose.
    Gre

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Anchoring Command Code to a Cell (Excel 2000 S

    Try if you can do this with my Flexfind utility (at The Excel MVP page listed below):

    - start Flexfind (control-shift-H) after installing it
    - search for $, make sure you limit the search to the selected sheet
    - check the "Objects" box
    - it should find all conditional format formulas
    - select the ones you need changed
    - replace with nothing
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    New Lounger
    Join Date
    Sep 2003
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Anchoring Command Code to a Cell (Excel 2000 S

    Hans and Unkamunka - many thanks - I tried both ways and was successful.

    I very much appreciate all of the help, and more importantly, your patience! [img]/forums/images/smilies/smile.gif[/img] You guys must sit in front of your computers sometimes and shake your heads, roll your eyes, or grimace in pain at some of the off-the-wall questions that come from knuckle-heads like me. A credit to you and all the other experts on this forum that you are always able to find some sort of solution.

    Best Regards

    DG

  11. #11
    New Lounger
    Join Date
    Sep 2003
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Anchoring Command Code to a Cell (Excel 2000 S

    Pieterse - Thanks for the tip. I'll download and check it out.

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Anchoring Command Code to a Cell (Excel 2000 S

    <hr>shake your heads, roll your eyes, or grimace in pain <hr>
    Which is why I love these smilies:

    <img src=/S/bash.gif border=0 alt=bash width=35 height=39> <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15> <img src=/S/crybaby.gif border=0 alt=crybaby width=15 height=15> <img src=/S/disappointed.gif border=0 alt=disappointed width=15 height=15> <img src=/S/drop.gif border=0 alt=drop width=23 height=23> <img src=/S/flatcat.gif border=0 alt=flatcat width=61 height=21> <img src=/S/frown.gif border=0 alt=frown width=15 height=15> <img src=/S/headthrob.gif border=0 alt=headthrob width=15 height=15> <img src=/S/igiveup.gif border=0 alt=igiveup width=31 height=23> <img src=/S/nuts.gif border=0 alt=nuts width=15 height=15> <img src=/S/puke.gif border=0 alt=puke width=60 height=15> <img src=/S/razz.gif border=0 alt=razz width=25 height=17> <img src=/S/rtfm.gif border=0 alt=rtfm width=24 height=23> <img src=/S/sad.gif border=0 alt=sad width=15 height=15> <img src=/S/scream.gif border=0 alt=scream width=15 height=15> <img src=/S/toilet.gif border=0 alt=toilet width=24 height=26> <img src=/S/wartgun.gif border=0 alt=wartgun width=73 height=24> <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #13
    New Lounger
    Join Date
    Sep 2003
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Anchoring Command Code to a Cell (Excel 2000 S

    heh, heh, heh.....that about sums it up! <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> ....and that is a fair representation of me....quite often lately, it seems.

Posting Permissions

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