Macro to only run in one column of one worksheet?

[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

 

[Don Guillett]

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

 

[CellShocked]

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.

 

[Dave Peterson]

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!!)

 

[Victor Delta]

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!!)

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]

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.

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!!)

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

 

[Puppet_Sock]

   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]

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

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