Add column to left of "hotspot"

[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

 

[Tom Ogilvy]

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

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!