Add column to left of "hotspot"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have read the previous threads and can now add a column to the right (or left) of the current active cell using

Sub AddColumnToTheLeft()
ActiveCell.Offset(0, 1).EntireColumn.Insert
End Sub

However, I need to write code that will add a column to the left of a "hotspot" graphic I have fixed to what is currently Column J. The idea is for the user to click on the yellow block and get a new blank column immediately to its left.

Obviously, each time I create a new column, the hotspot will move to Column K, L... etc. and the new column is always required to be the last (or right-hand) one of the series immediately before this hotspot

Therefore the macro I tried to record is also no use because it simply goes back to Column J each time and puts the new column to the left of that instead of to the left of the hotspot just clicked on

Please tell me what should come instead of "ActiveCell.Offset(0, 1)." to make this work — I assume this IS the bit I need to change

Thanks in advance

teaba
 
If you assign this macro to the graphic, it should work
Sub AddColumnToLeftOfHotSpot()
set rng = activesheet.shapes(Application.Caller).TopLeftCell
rng.EntireColumn.Insert
End Sub

--
Regards,
Tom Ogilvy


teabag said:
I have read the previous threads and can now add a column to the right (or
Click to expand...
left) of the current active cell using:
Sub AddColumnToTheLeft()
ActiveCell.Offset(0, 1).EntireColumn.Insert
End Sub

However, I need to write code that will add a column to the left of a
Click to expand...
"hotspot" graphic I have fixed to what is currently Column J. The idea is
for the user to click on the yellow block and get a new blank column
immediately to its left.
Obviously, each time I create a new column, the hotspot will move to
Click to expand...
Column K, L... etc. and the new column is always required to be the last (or
right-hand) one of the series immediately before this hotspot.
Therefore the macro I tried to record is also no use because it simply
Click to expand...
goes back to Column J each time and puts the new column to the left of that
instead of to the left of the hotspot just clicked on!
Please tell me what should come instead of "ActiveCell.Offset(0, 1)." to
Click to expand...
make this work - I assume this IS the bit I need to change!
 
Back
Top