Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ReadOnly TextBox from ControlSource
Hello,
I have a TextBox in a Form that has the ControlSource property set to read values from a Cell which has a formula. I have set Enabled to "false" so that users cannot enter data in the TextBox. (read-only from the cell) Somehow the formula in the linked cell is replaced by the value. Looks like the textbox is pushing the value to the cell. How can I prevent the TextBox to put any value into the LinkedCell. (It should just display whatever value is in the linked cell not not clear the formula) Thankyou. Gap |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ReadOnly TextBox from ControlSource
Drop the ControlSource and just populate the textbox via code:
Option Explicit Private Sub UserForm_Initialize() With Me.TextBox1 .Value = ActiveWorkbook.Worksheets("Sheet1").Range("a1").Te xt .Enabled = False End With End Sub Have you thought of using a Label instead of a textbox? wrote: Hello, I have a TextBox in a Form that has the ControlSource property set to read values from a Cell which has a formula. I have set Enabled to "false" so that users cannot enter data in the TextBox. (read-only from the cell) Somehow the formula in the linked cell is replaced by the value. Looks like the textbox is pushing the value to the cell. How can I prevent the TextBox to put any value into the LinkedCell. (It should just display whatever value is in the linked cell not not clear the formula) Thankyou. Gap -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ReadOnly TextBox from ControlSource
On Jun 5, 8:58 pm, Dave Peterson wrote:
Drop the ControlSource and just populate the textbox via code: Option Explicit Private Sub UserForm_Initialize() With Me.TextBox1 .Value = ActiveWorkbook.Worksheets("Sheet1").Range("a1").Te xt .Enabled = False End With End Sub Have you thought of using a Label instead of a textbox? wrote: Hello, I have a TextBox in a Form that has the ControlSource property set to read values from a Cell which has a formula. I have set Enabled to "false" so that users cannot enter data in the TextBox. (read-only from the cell) Somehow the formula in the linked cell is replaced by the value. Looks like the textbox is pushing the value to the cell. How can I prevent the TextBox to put any value into the LinkedCell. (It should just display whatever value is in the linked cell not not clear the formula) Thankyou. Gap -- Dave Peterson Thanks Dave. Yes thought about the Label, but it doesn't have a ControlSource. How to link a label to a Cell? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ReadOnly TextBox from ControlSource
You missed the intent of Dave's opening sentence... he suggested that you
NOT use the ControlSource at all... instead, just use the code he provided to assign the current value in the cell to the TextBox (or, in this case, the Label) when the UserForm initializes. Rick wrote in message ... On Jun 5, 8:58 pm, Dave Peterson wrote: Drop the ControlSource and just populate the textbox via code: Option Explicit Private Sub UserForm_Initialize() With Me.TextBox1 .Value = ActiveWorkbook.Worksheets("Sheet1").Range("a1").Te xt .Enabled = False End With End Sub Have you thought of using a Label instead of a textbox? wrote: Hello, I have a TextBox in a Form that has the ControlSource property set to read values from a Cell which has a formula. I have set Enabled to "false" so that users cannot enter data in the TextBox. (read-only from the cell) Somehow the formula in the linked cell is replaced by the value. Looks like the textbox is pushing the value to the cell. How can I prevent the TextBox to put any value into the LinkedCell. (It should just display whatever value is in the linked cell not not clear the formula) Thankyou. Gap -- Dave Peterson Thanks Dave. Yes thought about the Label, but it doesn't have a ControlSource. How to link a label to a Cell? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ReadOnly TextBox from ControlSource
On Jun 5, 11:54 pm, "Rick Rothstein \(MVP - VB\)"
wrote: You missed the intent of Dave's opening sentence... he suggested that you NOT use the ControlSource at all... instead, just use the code he provided to assign the current value in the cell to the TextBox (or, in this case, the Label) when the UserForm initializes. Rick wrote in message ... On Jun 5, 8:58 pm, Dave Peterson wrote: Drop the ControlSource and just populate the textbox via code: Option Explicit Private Sub UserForm_Initialize() With Me.TextBox1 .Value = ActiveWorkbook.Worksheets("Sheet1").Range("a1").Te xt .Enabled = False End With End Sub Have you thought of using a Label instead of a textbox? wrote: Hello, I have a TextBox in a Form that has the ControlSource property set to read values from a Cell which has a formula. I have set Enabled to "false" so that users cannot enter data in the TextBox. (read-only from the cell) Somehow the formula in the linked cell is replaced by the value. Looks like the textbox is pushing the value to the cell. How can I prevent the TextBox to put any value into the LinkedCell. (It should just display whatever value is in the linked cell not not clear the formula) Thankyou. Gap -- Dave Peterson Thanks Dave. Yes thought about the Label, but it doesn't have a ControlSource. How to link a label to a Cell? Thanks Rick. But then the value is just set once (when UserForm Initializes) and does not reflect the value when the Range Cell changes. (This is vbModeless form). Anyway I have found a workaround of setting the values in the Worksheet_Change event instead. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ReadOnly TextBox from ControlSource
Drop the ControlSource and just populate the textbox via code:
Option Explicit Private Sub UserForm_Initialize() With Me.TextBox1 .Value = ActiveWorkbook.Worksheets("Sheet1").Range("a1").Te xt .Enabled = False End With End Sub Have you thought of using a Label instead of a textbox? wrote: Hello, I have a TextBox in a Form that has the ControlSource property set to read values from a Cell which has a formula. I have set Enabled to "false" so that users cannot enter data in the TextBox. (read-only from the cell) Somehow the formula in the linked cell is replaced by the value. Looks like the textbox is pushing the value to the cell. How can I prevent the TextBox to put any value into the LinkedCell. (It should just display whatever value is in the linked cell not not clear the formula) Thankyou. Gap -- Dave Peterson Thanks Dave. Yes thought about the Label, but it doesn't have a ControlSource. How to link a label to a Cell? You missed the intent of Dave's opening sentence... he suggested that you NOT use the ControlSource at all... instead, just use the code he provided to assign the current value in the cell to the TextBox (or, in this case, the Label) when the UserForm initializes. Rick Thanks Rick. But then the value is just set once (when UserForm Initializes) and does not reflect the value when the Range Cell changes. (This is vbModeless form). Anyway I have found a workaround of setting the values in the Worksheet_Change event instead. You didn't mention the modeless part (actually, I don't remember you saying anything about a UserForm at all). Anyway, given your new information, the Change event would have been the next logical suggestion. For any future questions you may ask, it is always useful to provide as much detail as necessary to describe your situation (and problem) so the volunteers here don't have to guess at what your set up is. Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TextBox ControlSource | Excel Programming | |||
ControlSource in a userform textbox | Excel Programming | |||
textbox not updating when controlsource cell changes | Excel Programming | |||
Textbox ControlSource Property - Excel 97 | Excel Programming | |||
Problem with TextBox & ControlSource - Please Help | Excel Programming |