Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
active cell issue...
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
active cell issue...
You could select the topleftcell
ActiveSheet.Shapes(Application.Caller).TopLeftCell .Select -- HTH RP (remove nothere from the email address if mailing direct) "Murph" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
active cell issue...
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
active cell issue...
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
active cell issue...
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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Active Cell? = Iteration / Maximum Change Issue | Excel Discussion (Misc queries) | |||
Row select mode to highlight active row of active cell | Excel Discussion (Misc queries) | |||
Active X list box MultSelectExtened issue | Charts and Charting in Excel | |||
referring to formula in a non active cell from active cell | Excel Discussion (Misc queries) | |||
Select First Active Cell if the cell is in a pivot table | Excel Programming |