Linking Button to a cell

[MarcB]

I have 20 or so lines, each have a button next to them. If the use
clicks the button a line is inserted below the line the button is on
In theory this is a great idea, but the problem is that the butto
actions are linked to cells so once one line is added the rest ar
messed up. What I need is to be able to somehow code in vba the abilit
for the button to realize what line it is on so that it can generate
"I'm on line X, so I'll enter a blank line on X+1" instead of "I'm o
line C so I'll enter a new line on D".

If there is a easy way to do this I would really appreciate your input
My button clicking code looks like this:

Private Sub Button23_click()
'
' linebylinenotes Macro
' Macro recorded 10/1/2004 by mbryant
'

'
Selection.EntireRow.Insert
Range("C20:I20").Select
With Selection
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("B21").Select
ActiveCell.FormulaR1C1 = "Notes"
With ActiveCell.Characters(Start:=1, Length:=5).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("C22:F22").Select
End Sub

Any help would be greatly appreciated

 

[K Dales]

You have a great idea that would best be implemented with
a new Class that gives the buttons added functionality,
but that would take a lot of coding. No matter what you
do, it is likely to be pretty complex.

Here is one suggestion:

Buttons have various properties, some of which you are not
using. There are differences between the "Forms" toolbar
buttons and the "Control Toolbox" buttons - since yours
are named "ButtonXX" I will assume they are from the Forms
toolbar for this discussion.

The "Forms" buttons have a property "AlternativeText" that
would only be used for certain web implementations. If you
don't need it for that, you could store its row number in
that property.

Then, any time you need to have a cell range reference the
row the button is on, use its "AlternativeText" property,
e.g: Range("C" & Button23.AlternativeText & ":I" &
Button23.AlternativeText).Select

You could use the Offset function if you need to address
any cells relative to where the button currently is; e.g.
Range("A" & Button23.AlternativeText).Offset(-1,0) would
give the cell in the A column one row above your button.

The biggest problem: You would need some way to both read
the row numbers to find a button on a particular row and
to update the row numbers if you insert a row. You could
do this with a little bit of code:

Find a button:
Function FindButton(RowNo as Variant) as String
Dim CheckButton as String
Dim ButtonToCheck as Object

CheckButton = "" ' If CheckButton returns a blank string
then button was not found

For Each ButtonToCheck in Sheets("SheetName").Shapes
If ButtonToCheck.AlternativeText = RowNo Then
CheckButton = ButtonToCheck.Name
Next ButtonToCheck

FindButton = CheckButton

End Function

To update row numbers after an insert, a similar sub could
be written.

Not so simple, but may give you some ideas...