Linking dynamic Cells in Excel to Form
 

I have created a form that allows the user to enter information that will be displayed into the Excel Spreadsheet, such as a specific date. In the form properties, I set the control source to a cell within the spreadsheet. However, once I add or delete lines in the spreadsheet, the cell linkage within the form does not change dynamically. I have tried to apply Names in the control source. There must be something that will allow this.

Any suggestions would be great!

Linking dynamic Cells in Excel to Form
 

The controlsource needs Range as a string. So you can do this
Define a Name for the Cell you want as the ContolSource, it will move with inserts
Add this code to your Forms activate event , create more lines for each additional Textboxes

Two caveats:
the .Address will only refer to the ActiveSheet
If you make inserts while form is active it will not show up till Form is Deactvted/Reactivate

Private Sub UserForm_Activate(
Me.TextBox1.ControlSource = Range("DateRange").Addres
End Su

----- Novice wrote: ----

I have created a form that allows the user to enter information that will be displayed into the Excel Spreadsheet, such as a specific date. In the form properties, I set the control source to a cell within the spreadsheet. However, once I add or delete lines in the spreadsheet, the cell linkage within the form does not change dynamically. I have tried to apply Names in the control source. There must be something that will allow this

Any suggestions would be great!

Linking dynamic Cells in Excel to Form
 

Thanks for your input. However, I have tried to assign a name to the cell, but I am unsure of how to enter that information into the form. I tried putting something like = System Peak. But it gives me an error

Am I just missing something

Thanks

Linking dynamic Cells in Excel to Form
 

" The .Address will only refer to the ActiveSheet" Sorry this is WRONG
Use this and it will always refer to a specific sheet no matter what the activesheet is

Private Sub UserForm_Activate(
Me.TextBox1.ControlSource = Range("DateRange").Address(External:=True
End Su

----- chris wrote: ----

The controlsource needs Range as a string. So you can do this
Define a Name for the Cell you want as the ContolSource, it will move with inserts
Add this code to your Forms activate event , create more lines for each additional Textboxes

Two caveats:
the .Address will only refer to the ActiveSheet
If you make inserts while form is active it will not show up till Form is Deactvted/Reactivate

Private Sub UserForm_Activate(
Me.TextBox1.ControlSource = Range("DateRange").Addres
End Su

----- Novice wrote: ----

I have created a form that allows the user to enter information that will be displayed into the Excel Spreadsheet, such as a specific date. In the form properties, I set the control source to a cell within the spreadsheet. However, once I add or delete lines in the spreadsheet, the cell linkage within the form does not change dynamically. I have tried to apply Names in the control source. There must be something that will allow this

Any suggestions would be great!

Linking dynamic Cells in Excel to Form
 

All you have to do is define the the Name(s) for the cell(s)
Insert Name Define...
Then in your UserForm Activate Event add the code i gave you assinging the matching Cells Name to the matching textBox
The code will set the controlsource for you dynamically whenever the form is activated. Thats all
Also, make sure you check my correction posting.
----- Novice wrote: ----

Thanks for your input. However, I have tried to assign a name to the cell, but I am unsure of how to enter that information into the form. I tried putting something like = System Peak. But it gives me an error

Am I just missing something

Thanks

Linking dynamic Cells in Excel to Form
 

Hey

Thanks, alot. I failed to scroll down when reading your reply. Sorry about that. I have tried what you suggested and It works great

Thanks a whole bunc