Just playing around with userform. Pretty new to using them . Created a pretty simple userform that find me the percentage change between two values that I enter. (see image below). I'm just interested in going one step further, I want to know how to enter the values based on excel cells that I select. So in this example, if I select K6, the value 15 enters in current year and if I subsequently click on K8, the value 10 enters in base year). Hope that makes sense, let me know if that is possible. Thanks.
current code....
TextBox3_Change()
TextBox3.Value = Format((Val(TextBox1.Value) - Val(TextBox2.Value)) / Val(TextBox2.Value), "#0.00%")
Rather than clicking, why not load the values in those textboxes in the UserForm_Initialize()
event? Something like
Private Sub UserForm_Initialize()
TextBox1.Value = Sheets("Sheet1").Range("K6").Value2
End Sub
If you really want to select a cell (after the userform is shown) and then populate the textboxes, then yes that is also possible. Here is an example
For demonstration purpose, I am going to populate 1 textbox. Let's say our userform looks like this. Note, I added a CommandButton1
next to the textbox. I changed the caption to ...
. We will use Application.InputBox
with Type:=8
so that user can select a range.
Next paste this code in the userform code area
Option Explicit
Private Sub CommandButton1_Click()
Dim rng As Range
On Error Resume Next
'~~> Prompt user to select range
Set rng = Application.InputBox(Prompt:="Select the range", Type:=8)
On Error GoTo 0
'~~> Check if the user selected a range
If Not rng Is Nothing Then
'~~> Check if the range is single cell or not
If rng.Columns.Count > 1 Or rng.Rows.Count > 1 Then
MsgBox "Select single cell"
Exit Sub
End If
TextBox1.Text = rng.Value2
End If
End Sub
Demonstration
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments