Userform and Selecting Cell values

CleanRider

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%")

enter image description here

Siddharth Rout

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.

enter image description here

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

enter image description here

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related