Macro to only run in one column of one worksheet?

  • Thread starter Thread starter Victor Delta
  • Start date Start date
V

Victor Delta

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
 
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
Click to expand...

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.
 
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!!)
 
Dave Peterson said:
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!!)
Click to expand...

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
 
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.

Dave Peterson said:
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!!)
Click to expand...

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
Click to expand...
 
On Jan 16 said:
   if lcase(activesheet.name) <> lcase(wks.name) then
      exit sub 'not the right sheet
   end if
Click to expand...
[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
 
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 Jan 16 said:
if lcase(activesheet.name)<> lcase(wks.name) then
exit sub 'not the right sheet
end if
Click to expand...
[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
Click to expand...
 
Back
Top