Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing / Linking Dynamic Data or ASP Tables into Excel ? | Excel Discussion (Misc queries) | |||
Linking Form to worksheets excel 2007 | Excel Discussion (Misc queries) | |||
Linking cells for a form | Excel Worksheet Functions | |||
How 2 create an Excel Form using & linking items on Forms Toolbar | Excel Discussion (Misc queries) | |||
Web Form - Excel Linking | Excel Programming |