Macro to only run in one column of one worksheet?

Discussion in 'Microsoft Excel Discussion' started by Victor Delta, Jan 15, 2012.

  1. Victor Delta

    Victor Delta Guest

    Can anyone please tell me if it is possible to add some code to a macro so
    that it will only run in one particular worksheet of a spreadsheet and, if
    possible, only if the active cell is in a particular column?

    Many thanks,

    V
     
    Victor Delta, Jan 15, 2012
    #1
    1. Advertisements

  2. Victor Delta

    Don Guillett Guest

    As ALWAYS., pls post YOUR code for comments and suggestions.

    On Jan 15, 2:18 pm, "Victor Delta" <> wrote:
    > Can anyone please tell me if it is possible to add some code to a macro so
    > that it will only run in one particular worksheet of a spreadsheet and, if
    > possible, only if the active cell is in a particular column?
    >
    > Many thanks,
    >
    > V
     
    Don Guillett, Jan 15, 2012
    #2
    1. Advertisements

  3. Victor Delta

    CellShocked Guest

    On Sun, 15 Jan 2012 20:18:49 -0000, "Victor Delta" <>
    wrote:

    >Can anyone please tell me if it is possible to add some code to a macro so
    >that it will only run in one particular worksheet of a spreadsheet and, if
    >possible, only if the active cell is in a particular column?
    >
    >Many thanks,
    >
    >V


    You can give the column a named range range name. Then a formula
    referencing that named range only examines data within that range.

    Makes it easier to write the code to. Simply call the name out.
     
    CellShocked, Jan 16, 2012
    #3
  4. Option Explicit
    Sub testme()

    dim wks as worksheet
    dim myCol as long 'a number!!

    set wks = thisworkbook.worksheets("My Sheet Name Here")

    myCol = 5 'Column E is the 5th column

    if lcase(activesheet.name) <> lcase(wks.name) then
    exit sub 'not the right sheet
    end if

    if activecell.column <> mycol then
    exit sub
    end if

    'do your macro here

    End if

    Untested, uncompiled. Watch for typos!

    But you really don't need to do this verification to run against a certain
    column. You can run the macro and just operate on that data.

    For instance:

    Option Explicit
    Sub testme2()

    dim wks as worksheet
    dim myRng as range
    dim myCell as range

    set wks = thisworkbook.worksheets("My Sheet Name Here")

    with wks
    set myrng = .range("E1",.cells(.rows.count,"E").end(xlup))
    end with

    for each mycell in myrng.cells
    msgbox mycell.value
    next mycell

    End if

    It won't care what the active workbook is, or what the activesheet is, or where
    the activecell is.

    (All untested, uncompiled!!)


    On 01/15/2012 14:18, Victor Delta wrote:
    > Can anyone please tell me if it is possible to add some code to a macro so that
    > it will only run in one particular worksheet of a spreadsheet and, if possible,
    > only if the active cell is in a particular column?
    >
    > Many thanks,
    >
    > V


    --
    Dave Peterson
     
    Dave Peterson, Jan 16, 2012
    #4
  5. Victor Delta

    Victor Delta Guest

    "Dave Peterson" <> wrote in message
    news:jf1743$da1$...
    > Option Explicit
    > Sub testme()
    >
    > dim wks as worksheet
    > dim myCol as long 'a number!!
    >
    > set wks = thisworkbook.worksheets("My Sheet Name Here")
    >
    > myCol = 5 'Column E is the 5th column
    >
    > if lcase(activesheet.name) <> lcase(wks.name) then
    > exit sub 'not the right sheet
    > end if
    >
    > if activecell.column <> mycol then
    > exit sub
    > end if
    >
    > 'do your macro here
    >
    > End if
    >
    > Untested, uncompiled. Watch for typos!
    >
    > But you really don't need to do this verification to run against a certain
    > column. You can run the macro and just operate on that data.
    >
    > For instance:
    >
    > Option Explicit
    > Sub testme2()
    >
    > dim wks as worksheet
    > dim myRng as range
    > dim myCell as range
    >
    > set wks = thisworkbook.worksheets("My Sheet Name Here")
    >
    > with wks
    > set myrng = .range("E1",.cells(.rows.count,"E").end(xlup))
    > end with
    >
    > for each mycell in myrng.cells
    > msgbox mycell.value
    > next mycell
    >
    > End if
    >
    > It won't care what the active workbook is, or what the activesheet is, or
    > where the activecell is.
    >
    > (All untested, uncompiled!!)
    >
    >
    > On 01/15/2012 14:18, Victor Delta wrote:
    >> Can anyone please tell me if it is possible to add some code to a macro
    >> so that
    >> it will only run in one particular worksheet of a spreadsheet and, if
    >> possible,
    >> only if the active cell is in a particular column?
    >>
    >> Many thanks,
    >>
    >> V


    Many thanks for the helpful replies. This is my (amateur) code, and I only
    want to be able to run it when a cell in column G of a worksheet called
    "Category" has been selected. I guess if either criteria has not been
    matched, it would be perfect is an appropriate message box popped up.

    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "=R[1]C[-5]"
    Selection.Font.Bold = True
    Selection.Font.Underline = xlUnderlineStyleSingle
    ActiveCell.Resize(1, 6).Select
    Selection.Merge
    With Selection
    .HorizontalAlignment = xlLeft
    End With

    Thanks,

    V
     
    Victor Delta, Jan 16, 2012
    #5
  6. I don't see where you included any of the suggestion. Maybe you missed it when
    you pasted?

    Give it a try and post back with your results.

    On 01/16/2012 14:38, Victor Delta wrote:
    > "Dave Peterson" <> wrote in message
    > news:jf1743$da1$...
    >> Option Explicit
    >> Sub testme()
    >>
    >> dim wks as worksheet
    >> dim myCol as long 'a number!!
    >>
    >> set wks = thisworkbook.worksheets("My Sheet Name Here")
    >>
    >> myCol = 5 'Column E is the 5th column
    >>
    >> if lcase(activesheet.name) <> lcase(wks.name) then
    >> exit sub 'not the right sheet
    >> end if
    >>
    >> if activecell.column <> mycol then
    >> exit sub
    >> end if
    >>
    >> 'do your macro here
    >>
    >> End if
    >>
    >> Untested, uncompiled. Watch for typos!
    >>
    >> But you really don't need to do this verification to run against a certain
    >> column. You can run the macro and just operate on that data.
    >>
    >> For instance:
    >>
    >> Option Explicit
    >> Sub testme2()
    >>
    >> dim wks as worksheet
    >> dim myRng as range
    >> dim myCell as range
    >>
    >> set wks = thisworkbook.worksheets("My Sheet Name Here")
    >>
    >> with wks
    >> set myrng = .range("E1",.cells(.rows.count,"E").end(xlup))
    >> end with
    >>
    >> for each mycell in myrng.cells
    >> msgbox mycell.value
    >> next mycell
    >>
    >> End if
    >>
    >> It won't care what the active workbook is, or what the activesheet is, or
    >> where the activecell is.
    >>
    >> (All untested, uncompiled!!)
    >>
    >>
    >> On 01/15/2012 14:18, Victor Delta wrote:
    >>> Can anyone please tell me if it is possible to add some code to a macro so that
    >>> it will only run in one particular worksheet of a spreadsheet and, if possible,
    >>> only if the active cell is in a particular column?
    >>>
    >>> Many thanks,
    >>>
    >>> V

    >
    > Many thanks for the helpful replies. This is my (amateur) code, and I only want
    > to be able to run it when a cell in column G of a worksheet called "Category"
    > has been selected. I guess if either criteria has not been matched, it would be
    > perfect is an appropriate message box popped up.
    >
    > ActiveCell.Select
    > ActiveCell.FormulaR1C1 = "=R[1]C[-5]"
    > Selection.Font.Bold = True
    > Selection.Font.Underline = xlUnderlineStyleSingle
    > ActiveCell.Resize(1, 6).Select
    > Selection.Merge
    > With Selection
    > .HorizontalAlignment = xlLeft
    > End With
    >
    > Thanks,
    >
    > V


    --
    Dave Peterson
     
    Dave Peterson, Jan 17, 2012
    #6
  7. Victor Delta

    Puppet_Sock Guest

    On Jan 16, 8:01 am, Dave Peterson <> wrote:
    [snips]
    >    if lcase(activesheet.name) <> lcase(wks.name) then
    >       exit sub 'not the right sheet
    >    end if

    [snips]

    Why do you use lcase here? Maybe I'm wrong, but I don't
    think sheet names can differ only by case of the chars in
    their names. So it would seem lcase won't affect the
    outcome here.
    Socks
     
    Puppet_Sock, Jan 18, 2012
    #7
  8. Try it to find out for sure.

    And since the developer would be typing the name into the code, I wanted to
    protect against his typing and if the user or developer changed the name on the tab.

    ps. Maybe you use:
    Option Compare Text

    At the top of your code????

    On 01/18/2012 14:19, Puppet_Sock wrote:
    > On Jan 16, 8:01 am, Dave Peterson<> wrote:
    > [snips]
    >> if lcase(activesheet.name)<> lcase(wks.name) then
    >> exit sub 'not the right sheet
    >> end if

    > [snips]
    >
    > Why do you use lcase here? Maybe I'm wrong, but I don't
    > think sheet names can differ only by case of the chars in
    > their names. So it would seem lcase won't affect the
    > outcome here.
    > Socks


    --
    Dave Peterson
     
    Dave Peterson, Jan 19, 2012
    #8
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. toontje

    [Fwd: Run macro on exit worksheet/workbook]

    toontje, Aug 4, 2005, in forum: Microsoft Excel Discussion
    Replies:
    0
    Views:
    252
    toontje
    Aug 4, 2005
  2. Replies:
    1
    Views:
    250
    Pete_UK
    Aug 1, 2006
  3. MOC835

    Run Macro when Worksheet is changed...

    MOC835, May 29, 2007, in forum: Microsoft Excel Discussion
    Replies:
    1
    Views:
    222
    Dave Peterson
    May 29, 2007
  4. BillRobPV

    macro will not run with file.xls!macro.macro

    BillRobPV, Sep 12, 2008, in forum: Microsoft Excel Discussion
    Replies:
    2
    Views:
    424
    BillRobPV
    Sep 12, 2008
  5. Don

    Force macro to run on a specific worksheet

    Don, Jun 5, 2009, in forum: Microsoft Excel Discussion
    Replies:
    2
    Views:
    302
Loading...

Share This Page