Display Data in a userform (textbox)

[mrjayps]

Hi - I have a userform and am displaying data from Worksheet "Price_List" Cell G12. I am able to display it by entering Price_List!G12 into control source, however once the userform is activated, the value overides the formula in my spreadsheet. How am I able to simply display the data, and retain the formula in the spreadsheet.

If variables on the userform/spreadsheet change, I require the data display on the userform to change too.

Many thanks

 

[sykes]

Please tell us how the userform is being displayed (activated).

 

[mrjayps]

Hi,

I activate it by clicking on a button as below..

thks

Sub RoundedRectangle1_Click()

frmQTool.Show

End Sub

 

[VBACO]

Populate the textbox during the load or open process

Me.Txtbox1 = Worksheets("Price_List").Range(G12)

 

[sykes]

The problem with using the ControlSource is that it's a two-way street, so anything the user enters into the TextBox once the userform's active, is also reflected in the "Linked" cell (which is the behaviour you describe).
There are may ways around this. Here are just two ideas:
1. If you don't actually need a textbox (i.e. you only want to display data - not edit it) I always find a label is the best bet - using the label's "Caption" property.
a. Put a new label on your userform - make sure it's long enough to take the text from your cell.
b. Use the userform's _initialize event to set the label's caption:

Private Sub UserForm_Initialize()
Me.Label1.Caption = Sheets("Price_List").Range("G12").Value
End Sub

2. If you really need a textbox (not sure why) then get rid of the ControlSource entry, and set the TextBox's value with the _initialize event - as before:

Private Sub UserForm_Initialize()
Me.TextBox1.value = Sheets("Price_List").Range("G12").Value
End Sub

.......change the textbox name to suit.


Finally, you could have the userform permanently visible, then reflect any changes using the sheet's _change event.

Standby for that..........

 

[sykes]

To have the userform permanently visible whilst your user still works on the sheet:

Sub RoundedRectangle1_Click()

frmQTool.Show Modeless

End Sub

Then, to ensure that any change in G12 is reflected in the label (or your textbox):

Private Sub Worksheet_Change(ByVal Target As Range)
frmQTool.Label1.Caption = Me.Range("G12").Value
End Sub

Once again - change the reference to the label or textbox, to that of yours.

 

[mrjayps]

thanks very much

 

[kirgan]

I need help generating a pop up window(table) in excel. I want a window to pop up displaying everything within a specific range say from (A1:X30) from a different sheet in excel when I click on a command button.

 

[My Aswer Is This]

You posting title says:Display Data in a userform (textbox)

But then you say:
I need help generating a pop up window(table)

Which is it a UserForm Textbox?
Or a Sheet(Table)

And it would be impossible to put all the data from Cells (A1:X30) all into one UserForm Texbox and be readable.

 

[kirgan]

You posting title says:Display Data in a userform (textbox)

But then you say:
I need help generating a pop up window(table)

Which is it a UserForm Textbox?
Or a Sheet(Table)

And it would be impossible to put all the data from Cells (A1:X30) all into one UserForm Texbox and be readable.

how about a userform textbox that extends according to the content? if you can give me the code to play around with, i'd really appreciate it.
Thank you