active cell issue...

I appreciate everyone's assistance first off.

I have a macro inside of an autoshape. My issue is that the macro relies on
the cell that is currently selected to gather information. So if the cell one
row down from the macro is selected I get the wrong information. Way too much
room for user error.

Can I set the macro to make the cell that the autoshape is located within
the active cell? So when the autoshape is clicked it automatically makes that
cell the selected cell?

the current macro reads like this....

Sub Barcode()
Dim ActRow As Integer
Dim Iloop As Integer
Dim shp As Shape
Dim rng As Range
Set shp = ActiveSheet.Shapes(Application.Caller)
Set rng = shp.TopLeftCell.Offset(0, 1)
rng.Value = Now

Application.ScreenUpdating = False
ActRow = ActiveCell.Row
Columns("A:B").Insert

For Iloop = 1 To 6
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(ActRow, Iloop + 2)
Next Iloop
For Iloop = 12 To 15
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(ActRow, Iloop + 2)
Next Iloop

Worksheets("Counts").Rows.RowHeight = 40

With Worksheets("Counts").Rows(10)
.RowHeight = .RowHeight * 3
End With
With Worksheets("Counts").Columns("A")
.ColumnWidth = .ColumnWidth * 5
End With
With Worksheets("Counts").Columns("B")
.ColumnWidth = .ColumnWidth * 8
End With
With Worksheets("Counts").Range("A1:B9")
.Font.Size = 30
With Worksheets("Counts").Range("B10")
.Font.Size = 160
End With
End With
Worksheets("Counts").Range("B10").Font.Name = "Free 3 of 9"

Range("A1:B15").PrintOut Copies:=1, Collate:=True

Worksheets("Counts").Rows.RowHeight = 25

Columns("A:B").Delete

Application.ScreenUpdating = False


End Sub

no need to change the selection, just don't depend on it

change

ActRow = ActiveCell.Row

to

ActRow = rng.row

This assumes you have your shapes on the row you want to work with.

--
Regards,
Tom Ogilvy


"Murph" <[email protected]> wrote in message
news:[email protected]...
> I appreciate everyone's assistance first off.
>
> I have a macro inside of an autoshape. My issue is that the macro relies
on
> the cell that is currently selected to gather information. So if the cell
one
> row down from the macro is selected I get the wrong information. Way too
much
> room for user error.
>
> Can I set the macro to make the cell that the autoshape is located within
> the active cell? So when the autoshape is clicked it automatically makes
that
> cell the selected cell?
>
> the current macro reads like this....
>
> Sub Barcode()
> Dim ActRow As Integer
> Dim Iloop As Integer
> Dim shp As Shape
> Dim rng As Range
> Set shp = ActiveSheet.Shapes(Application.Caller)
> Set rng = shp.TopLeftCell.Offset(0, 1)
> rng.Value = Now
>
> Application.ScreenUpdating = False
> ActRow = ActiveCell.Row
> Columns("A:B").Insert
>
> For Iloop = 1 To 6
> Cells(Iloop, "A") = Cells(2, Iloop + 2)
> Cells(Iloop, "B") = Cells(ActRow, Iloop + 2)
> Next Iloop
> For Iloop = 12 To 15
> Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
> Cells(Iloop - 5, "B") = Cells(ActRow, Iloop + 2)
> Next Iloop
>
> Worksheets("Counts").Rows.RowHeight = 40
>
> With Worksheets("Counts").Rows(10)
> .RowHeight = .RowHeight * 3
> End With
> With Worksheets("Counts").Columns("A")
> .ColumnWidth = .ColumnWidth * 5
> End With
> With Worksheets("Counts").Columns("B")
> .ColumnWidth = .ColumnWidth * 8
> End With
> With Worksheets("Counts").Range("A1:B9")
> .Font.Size = 30
> With Worksheets("Counts").Range("B10")
> .Font.Size = 160
> End With
> End With
> Worksheets("Counts").Range("B10").Font.Name = "Free 3 of 9"
>
> Range("A1:B15").PrintOut Copies:=1, Collate:=True
>
> Worksheets("Counts").Rows.RowHeight = 25
>
> Columns("A:B").Delete
>
> Application.ScreenUpdating = False
>
>
> End Sub

thanks Tom I seriously appreciate everything.

Figure I'll start to learn this the more you guys keep helping me. Bit off a
lil more than I could chew for my first try at macro's.

"Tom Ogilvy" wrote:

> no need to change the selection, just don't depend on it
>
> change
>
> ActRow = ActiveCell.Row
>
> to
>
> ActRow = rng.row
>
> This assumes you have your shapes on the row you want to work with.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Murph" <[email protected]> wrote in message
> news:[email protected]...
> > I appreciate everyone's assistance first off.
> >
> > I have a macro inside of an autoshape. My issue is that the macro relies
> on
> > the cell that is currently selected to gather information. So if the cell
> one
> > row down from the macro is selected I get the wrong information. Way too
> much
> > room for user error.
> >
> > Can I set the macro to make the cell that the autoshape is located within
> > the active cell? So when the autoshape is clicked it automatically makes
> that
> > cell the selected cell?
> >
> > the current macro reads like this....
> >
> > Sub Barcode()
> > Dim ActRow As Integer
> > Dim Iloop As Integer
> > Dim shp As Shape
> > Dim rng As Range
> > Set shp = ActiveSheet.Shapes(Application.Caller)
> > Set rng = shp.TopLeftCell.Offset(0, 1)
> > rng.Value = Now
> >
> > Application.ScreenUpdating = False
> > ActRow = ActiveCell.Row
> > Columns("A:B").Insert
> >
> > For Iloop = 1 To 6
> > Cells(Iloop, "A") = Cells(2, Iloop + 2)
> > Cells(Iloop, "B") = Cells(ActRow, Iloop + 2)
> > Next Iloop
> > For Iloop = 12 To 15
> > Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
> > Cells(Iloop - 5, "B") = Cells(ActRow, Iloop + 2)
> > Next Iloop
> >
> > Worksheets("Counts").Rows.RowHeight = 40
> >
> > With Worksheets("Counts").Rows(10)
> > .RowHeight = .RowHeight * 3
> > End With
> > With Worksheets("Counts").Columns("A")
> > .ColumnWidth = .ColumnWidth * 5
> > End With
> > With Worksheets("Counts").Columns("B")
> > .ColumnWidth = .ColumnWidth * 8
> > End With
> > With Worksheets("Counts").Range("A1:B9")
> > .Font.Size = 30
> > With Worksheets("Counts").Range("B10")
> > .Font.Size = 160
> > End With
> > End With
> > Worksheets("Counts").Range("B10").Font.Name = "Free 3 of 9"
> >
> > Range("A1:B15").PrintOut Copies:=1, Collate:=True
> >
> > Worksheets("Counts").Rows.RowHeight = 25
> >
> > Columns("A:B").Delete
> >
> > Application.ScreenUpdating = False
> >
> >
> > End Sub
>
>
>

quick question... what does the "Iloop-5" do in the second part of the loop
here? I understand the columns are being removed.. does the - 5 eliminate a
group of 5 whitespaces?

For Iloop = 1 To 6
Cells(Iloop, "A") = Cells(2, Iloop + 2)
Cells(Iloop, "B") = Cells(ActRow, Iloop + 2)
Next Iloop
For Iloop = 12 To 15
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(ActRow, Iloop + 2)
Next Iloop

"Tom Ogilvy" wrote:

> no need to change the selection, just don't depend on it
>
> change
>
> ActRow = ActiveCell.Row
>
> to
>
> ActRow = rng.row
>
> This assumes you have your shapes on the row you want to work with.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Murph" <[email protected]> wrote in message
> news:[email protected]...
> > I appreciate everyone's assistance first off.
> >
> > I have a macro inside of an autoshape. My issue is that the macro relies
> on
> > the cell that is currently selected to gather information. So if the cell
> one
> > row down from the macro is selected I get the wrong information. Way too
> much
> > room for user error.
> >
> > Can I set the macro to make the cell that the autoshape is located within
> > the active cell? So when the autoshape is clicked it automatically makes
> that
> > cell the selected cell?
> >
> > the current macro reads like this....
> >
> > Sub Barcode()
> > Dim ActRow As Integer
> > Dim Iloop As Integer
> > Dim shp As Shape
> > Dim rng As Range
> > Set shp = ActiveSheet.Shapes(Application.Caller)
> > Set rng = shp.TopLeftCell.Offset(0, 1)
> > rng.Value = Now
> >
> > Application.ScreenUpdating = False
> > ActRow = ActiveCell.Row
> > Columns("A:B").Insert
> >
> > For Iloop = 1 To 6
> > Cells(Iloop, "A") = Cells(2, Iloop + 2)
> > Cells(Iloop, "B") = Cells(ActRow, Iloop + 2)
> > Next Iloop
> > For Iloop = 12 To 15
> > Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
> > Cells(Iloop - 5, "B") = Cells(ActRow, Iloop + 2)
> > Next Iloop
> >
> > Worksheets("Counts").Rows.RowHeight = 40
> >
> > With Worksheets("Counts").Rows(10)
> > .RowHeight = .RowHeight * 3
> > End With
> > With Worksheets("Counts").Columns("A")
> > .ColumnWidth = .ColumnWidth * 5
> > End With
> > With Worksheets("Counts").Columns("B")
> > .ColumnWidth = .ColumnWidth * 8
> > End With
> > With Worksheets("Counts").Range("A1:B9")
> > .Font.Size = 30
> > With Worksheets("Counts").Range("B10")
> > .Font.Size = 160
> > End With
> > End With
> > Worksheets("Counts").Range("B10").Font.Name = "Free 3 of 9"
> >
> > Range("A1:B15").PrintOut Copies:=1, Collate:=True
> >
> > Worksheets("Counts").Rows.RowHeight = 25
> >
> > Columns("A:B").Delete
> >
> > Application.ScreenUpdating = False
> >
> >
> > End Sub
>
>
>

For Iloop = 12 To 15
Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
Cells(Iloop - 5, "B") = Cells(ActRow, Iloop + 2)
Next Iloop

when Iloop = 12 on the first trip through the loop, it will do this

Range("A7").value = Range("N2").Value

iloop - 5 = 12 - 5 = 7; Cells(7,"A") is A7
iloop + 2 = 14; Cells(2,14) is N2 column 14 is the N column.

you should be able to figure out the rest.

--
Regards,
Tom Ogilvy



"Murph" <[email protected]> wrote in message
news:[email protected]...
> quick question... what does the "Iloop-5" do in the second part of the
loop
> here? I understand the columns are being removed.. does the - 5 eliminate
a
> group of 5 whitespaces?
>
> For Iloop = 1 To 6
> Cells(Iloop, "A") = Cells(2, Iloop + 2)
> Cells(Iloop, "B") = Cells(ActRow, Iloop + 2)
> Next Iloop
> For Iloop = 12 To 15
> Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
> Cells(Iloop - 5, "B") = Cells(ActRow, Iloop + 2)
> Next Iloop
>
> "Tom Ogilvy" wrote:
>
> > no need to change the selection, just don't depend on it
> >
> > change
> >
> > ActRow = ActiveCell.Row
> >
> > to
> >
> > ActRow = rng.row
> >
> > This assumes you have your shapes on the row you want to work with.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Murph" <[email protected]> wrote in message
> > news:[email protected]...
> > > I appreciate everyone's assistance first off.
> > >
> > > I have a macro inside of an autoshape. My issue is that the macro
relies
> > on
> > > the cell that is currently selected to gather information. So if the
cell
> > one
> > > row down from the macro is selected I get the wrong information. Way
too
> > much
> > > room for user error.
> > >
> > > Can I set the macro to make the cell that the autoshape is located
within
> > > the active cell? So when the autoshape is clicked it automatically
makes
> > that
> > > cell the selected cell?
> > >
> > > the current macro reads like this....
> > >
> > > Sub Barcode()
> > > Dim ActRow As Integer
> > > Dim Iloop As Integer
> > > Dim shp As Shape
> > > Dim rng As Range
> > > Set shp = ActiveSheet.Shapes(Application.Caller)
> > > Set rng = shp.TopLeftCell.Offset(0, 1)
> > > rng.Value = Now
> > >
> > > Application.ScreenUpdating = False
> > > ActRow = ActiveCell.Row
> > > Columns("A:B").Insert
> > >
> > > For Iloop = 1 To 6
> > > Cells(Iloop, "A") = Cells(2, Iloop + 2)
> > > Cells(Iloop, "B") = Cells(ActRow, Iloop + 2)
> > > Next Iloop
> > > For Iloop = 12 To 15
> > > Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
> > > Cells(Iloop - 5, "B") = Cells(ActRow, Iloop + 2)
> > > Next Iloop
> > >
> > > Worksheets("Counts").Rows.RowHeight = 40
> > >
> > > With Worksheets("Counts").Rows(10)
> > > .RowHeight = .RowHeight * 3
> > > End With
> > > With Worksheets("Counts").Columns("A")
> > > .ColumnWidth = .ColumnWidth * 5
> > > End With
> > > With Worksheets("Counts").Columns("B")
> > > .ColumnWidth = .ColumnWidth * 8
> > > End With
> > > With Worksheets("Counts").Range("A1:B9")
> > > .Font.Size = 30
> > > With Worksheets("Counts").Range("B10")
> > > .Font.Size = 160
> > > End With
> > > End With
> > > Worksheets("Counts").Range("B10").Font.Name = "Free 3 of 9"
> > >
> > > Range("A1:B15").PrintOut Copies:=1, Collate:=True
> > >
> > > Worksheets("Counts").Rows.RowHeight = 25
> > >
> > > Columns("A:B").Delete
> > >
> > > Application.ScreenUpdating = False
> > >
> > >
> > > End Sub
> >
> >
> >

You could select the topleftcell

ActiveSheet.Shapes(Application.Caller).TopLeftCell.Select

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Murph" <[email protected]> wrote in message
news:[email protected]...
> I appreciate everyone's assistance first off.
>
> I have a macro inside of an autoshape. My issue is that the macro relies
on
> the cell that is currently selected to gather information. So if the cell
one
> row down from the macro is selected I get the wrong information. Way too
much
> room for user error.
>
> Can I set the macro to make the cell that the autoshape is located within
> the active cell? So when the autoshape is clicked it automatically makes
that
> cell the selected cell?
>
> the current macro reads like this....
>
> Sub Barcode()
> Dim ActRow As Integer
> Dim Iloop As Integer
> Dim shp As Shape
> Dim rng As Range
> Set shp = ActiveSheet.Shapes(Application.Caller)
> Set rng = shp.TopLeftCell.Offset(0, 1)
> rng.Value = Now
>
> Application.ScreenUpdating = False
> ActRow = ActiveCell.Row
> Columns("A:B").Insert
>
> For Iloop = 1 To 6
> Cells(Iloop, "A") = Cells(2, Iloop + 2)
> Cells(Iloop, "B") = Cells(ActRow, Iloop + 2)
> Next Iloop
> For Iloop = 12 To 15
> Cells(Iloop - 5, "A") = Cells(2, Iloop + 2)
> Cells(Iloop - 5, "B") = Cells(ActRow, Iloop + 2)
> Next Iloop
>
> Worksheets("Counts").Rows.RowHeight = 40
>
> With Worksheets("Counts").Rows(10)
> .RowHeight = .RowHeight * 3
> End With
> With Worksheets("Counts").Columns("A")
> .ColumnWidth = .ColumnWidth * 5
> End With
> With Worksheets("Counts").Columns("B")
> .ColumnWidth = .ColumnWidth * 8
> End With
> With Worksheets("Counts").Range("A1:B9")
> .Font.Size = 30
> With Worksheets("Counts").Range("B10")
> .Font.Size = 160
> End With
> End With
> Worksheets("Counts").Range("B10").Font.Name = "Free 3 of 9"
>
> Range("A1:B15").PrintOut Copies:=1, Collate:=True
>
> Worksheets("Counts").Rows.RowHeight = 25
>
> Columns("A:B").Delete
>
> Application.ScreenUpdating = False
>
>
> End Sub