PC Review


Reply
Thread Tools Rate Thread

Macro to only run in one column of one worksheet?

 
 
Victor Delta
Guest
Posts: n/a
 
      15th Jan 2012
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

 
Reply With Quote
 
 
 
 
Don Guillett
Guest
Posts: n/a
 
      16th Jan 2012
As ALWAYS., pls post YOUR code for comments and suggestions.

On Jan 15, 2:18*pm, "Victor Delta" <(E-Mail Removed)> 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


 
Reply With Quote
 
 
 
 
CellShocked
Guest
Posts: n/a
 
      16th Jan 2012
On Sun, 15 Jan 2012 20:18:49 -0000, "Victor Delta" <(E-Mail Removed)>
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.
 
Reply With Quote
 
Dave Peterson
Guest
Posts: n/a
 
      16th Jan 2012
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
 
Reply With Quote
 
Victor Delta
Guest
Posts: n/a
 
      16th Jan 2012
"Dave Peterson" <(E-Mail Removed)> wrote in message
news:jf1743$da1$(E-Mail Removed)...
> 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

 
Reply With Quote
 
Dave Peterson
Guest
Posts: n/a
 
      17th Jan 2012
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" <(E-Mail Removed)> wrote in message
> news:jf1743$da1$(E-Mail Removed)...
>> 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
 
Reply With Quote
 
Puppet_Sock
Guest
Posts: n/a
 
      18th Jan 2012
On Jan 16, 8:01*am, Dave Peterson <(E-Mail Removed)> 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
 
Reply With Quote
 
Dave Peterson
Guest
Posts: n/a
 
      19th Jan 2012
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<(E-Mail Removed)> 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
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: 1st macro run then 2 macro is run Don Guillett Microsoft Excel Programming 0 16th Feb 2012 01:42 AM
Run macro on startup only if it has not been run previously today NeoFax Microsoft Access Macros 1 2nd Jul 2010 11:56 PM
Activate second macro only after the first macro is run Ruchir Microsoft Excel Worksheet Functions 8 1st Aug 2008 06:50 PM
Record Macro in Excel, run macro untill last column Oamya Kim Microsoft Excel Programming 1 29th Jul 2008 08:29 AM
Macro to run or not run a second Macro Darin Kramer Microsoft Excel Programming 2 2nd Dec 2004 05:27 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 10:47 AM.