Cell Anchoring in VBA??

[tomtom412]

So Im using Range("A1").value=textbox1.value etc to assign text or numbers to certain cells in a large table.

The problem is that i want to achor that value to that cell, like what can be done in normal excel.

So the code I want ideally is:

Ranges("$A1").Value = TextBox1.Value

Or something along those lines

Is this possible in <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym>? It would make my like 100% easier. turning a project from months to weeks!

Thank you
Tom

 

[derHoepp]

Hi Tom,

I don't know if I got you right, but if it's a UserForm, you could loop over all Textboxes in the Controls()-Collection and assign their value to a dynamic Range:

Sub Test()
   Dim i As Long
   For i = 1 To 10
      Sheet1.Cells(i,1).Value = Me.Controls("TextBox" & i).Value
   Next i
End Sub

If you have Textboxes (Form Controls) on your sheet, you might just want to use the LinkedCell-Property. You could also try and use the .Tag-Property of the Textboxes, to save the belonging cell.

derHoepp

 

[tomtom412]

If you have Textboxes (Form Controls) on your sheet, you might just want to use the LinkedCell-Property. You could also try and use the .Tag-Property of the Textboxes, to save the belonging cell.

derHoepp

derHoepp

Can you tell me more about the LinkedCell property and the Tag property?
I have no textboxes on my sheet, however theyre used extensively in the userforms I use to populate information on this sheet. So there are several various userforms that are used with textboxes, checkboxes, comboboxes, options etc etc from vba to populate my sheet with data.

The problem im having is that once rows are inserted, deleted etc in the sheet, the cell references that were hardcoded into the userforms are no longer the cells that I want data to go into because the userforms dont have anchoring as such, (i.e. I cant go Range("A$1").value=textbox.value" as if I was hand writing formulas into the sheet like normal. )

So my question is, does that .Tag property do this? Cant seem to make it work on my own in vba.

Thanks for your time

Tom

 

[My Aswer Is This]

I think you need to use Deifined Named Ranges.

Like this:
Range("Mike").Value = TextBox1.Value