PDA

View Full Version : Cell Anchoring in VBA??



tomtom412
03-12-2017, 11:54 AM
Hi

So I've got a sheet that is linked to several userforms via VBA and these userforms are used by the user to populate data and information to the respective cells in my sheet!

Now thats all well and good, and works just as I want it, until I have to make a change to sheet, in the form of adding or deleting rows.

When this happens, because i've hardcoded all the cell references into the code functions, e.g.


Range("A1").Value = TextBox1.Value


I would then have to go back and re-assign the correct cell references for where I want data to go from the userform.

My question is: Is is possible to have some sort of anchoring or relative referencing in vba, as there is when hand writing in formulas in excel using "$" to signify what you want to stay constant when references move? I have only started using vba recently and so my knowledge of the capabilities of the program is limited so I would be incredibly grateful for any help whatsoever!

I've attached a sample representation of what my original worksheet looks like and functions: a simple set of variable data/info is inputted using a userform activated by a command button on the sheet! Any help or advice on how to do this better would be amazing!

Thanks
Tom

mdmackillop
03-12-2017, 12:19 PM
You could use Find to allow flexibility

Private Sub Submitcmd_Click()
With Columns(3)
.Find("Program:", lookat:=xlWhole).Offset(, 1).Value = Programtxt.Value
'etc.
End With

rlv
03-12-2017, 03:43 PM
Have you considered using named ranges for your data cells instead of cell addresses? For example if you named cell D4 to be "Program" then you can do this:

Private Sub Submitcmd_Click()
ThisWorkbook.Names("Program").RefersToRange.Value = Programtxt.Value
Unload Me
End Sub

Leith Ross
03-12-2017, 03:52 PM
FYI - Cross Posted

Cell Anchoring in VBA?? (https://www.excelforum.com/excel-programming-vba-macros/1177336-cell-anchoring-in-vba.html)

SamT
03-12-2017, 08:02 PM
You used Merged Cells!!!!! Bad Lad, no biscuits for you. Never Ever Merge Cells that VBA or Excel will be operating on.

Named Ranges Can be moved all over the place without affecting the way they are referred to.For Example

Sheet1.Range("Paint") = Me.TextBox1.Text
Me.TextBox1.Text = Sheet1.Range("Paint") even after you move it

In the attached. note the two Pasted Lists, 1 was pasted after I had Excel Create the Names and 2 was after I moved the Paint Range and Inserted two rows into the Rejects and Accept Ranges

There are rules to naming Ranges: Can't start with a number, can't have spaces, can't have any punctuation but UnderScores. Have to be pretty short (32 characters?) Look at the code in the attached.