ReadOnly TextBox from ControlSource

[gappodi]

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]

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").Text
.Enabled = False
End With
End Sub

Have you thought of using a Label instead of a textbox?

 

[eagleyzw]

I think you can use a Label instead of a textbox too

It is a good idea

 

[gappodi]

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").Text
.Enabled = False
End With
End Sub

Have you thought of using a Label instead of a textbox?

Thanks Dave.
Yes thought about the Label, but it doesn't have a ControlSource.
How to link a label to a Cell?

 

[Rick Rothstein \(MVP - VB\)]

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

 

[gappodi]

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.

 

[Rick Rothstein \(MVP - VB\)]

Drop the ControlSource and just populate the textbox via code:

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