# Automatically populate cell values in MS Excel UserForm TextBox



## rywags11 (May 23, 2012)

Hello

I have a UserForm that I want to open when a user clicks a cell in a table associated with a unique ID, and automatically populate portions of the data in some of the TextBox.

Column A is labeled SKU
Column B is labeled Qty
Column C is labeled Demand
Column D is labeled Recommendation

The UserForm has a TextBox for SKU, Qty, and Demand and also several others that the user will input data into.

I want the SKU, Qty, and Demand TextBox (respectively) to automatically populate the information that is in the associated fields. 

For example:
Cell A1 has the value 1234-567
Cell B1 has the value 10,000
Cell C1 has the value 27,000
Cell D1 has the value Replenish

The user will click on cell D1 to activate the UserForm

When the UserForm is populated; SKU, Qty, and Demand TextBox contain the values associated with row 1 (see example above).

The user should be able to click any cell in column D to not only activate the UserForm, but the row clicked should always populate the unique values in columns A, B, and C based upon which row was clicked in column D.

I have the UserForm created with the applicable fields, but this programming is beyond me and any help someone could offer would be very much appreciated!

Thank you!

-Ryan


----------



## AlbertMC2 (Jul 15, 2010)

Hi

Try this:

I have a sheet (Sheet1) which is populated with data as you said.
A User form (UserForm1) with 4 text boxes (TextBox1 - TextBox4)

Then create workbook code. Press Alt + F11 for the VB Editor.
On the left side double click "ThisWorkbook" and paste the following code as a new procedure.

```
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    
    If (Target.Column = 4) And (ActiveSheet.Name = "Sheet1") Then
        UserForm1.Show
    End If
    
End Sub
```
Every time a cell in Column D on Sheet1 is clicked the user form will open.

Now in the VB editor again, on the left side, right click your user form and select "View Code". Add/Paste the following code:

```
Private Sub UserForm_Activate()
    
    Dim ActiveR As Long
        ActiveR = ActiveCell.Row
     
    TextBox1.Value = Cells(ActiveR, 1).Value
    TextBox2.Value = Cells(ActiveR, 2).Value
    TextBox3.Value = Cells(ActiveR, 3).Value
    TextBox4.Value = Cells(ActiveR, 4).Value
End Sub
```
This will populate the user form.
*ActiveR = ActiveCell.Row* gives you the row number of the cell you clicked
the values 1-4 are the column numbers (ie A-D)


----------



## rywags11 (May 23, 2012)

Thanks! That worked out great!!

Can you tell me how I would do the same thing as we've been discussing, but also automatically populate fields in the same UserForm from a different tab, based upon the value in Column A?

For example:
In Worksheet "Summary" (the one we've been working on) Column A is labeled as SKU
Cell A1 (in the same worksheet) has the value 1234-567 
Another worksheet called "History" has a table with all SKU's & their order history by month (column B = January, column C = February, column D = March, etc)
When the UserForm pops up with the auto-populated values now running from the "Summary" worksheet, I also have a seperate field for all 12 months in the year in that same UserForm. I need the UserForm to look into the "History" worksheet based on the SKU value & auto-populate the value that is in each respective month field of the tab.

Can you offer any help on that one too? Sorry for not including it originally, but it came up after I drafted the original post. 

Thank you again!!

-Ryan


----------



## AlbertMC2 (Jul 15, 2010)

Will the data be on the *same row* in sheet "Summary" as well as sheet "History" ?

But instead of *TextBox1.Value = Cells(ActiveR, 1).Value*
use something like:
*TextBox1.Value = Worksheets("History").Range("A1").Value*

Edit: Sorry I don't think I read your post properly.
Let me clarify:

On sheet Summary you click on a cell in Column D.
The userform appears and populates.
It then finds the value in column A (on the same row you clicked)
It then finds that value on sheet History and
Populates the other Jan-Dec fields. ?
On sheet History do you have an SKU column (perhaps column A?)
and then columns next to it (B-M?) for Jan-Dec ?


----------



## rywags11 (May 23, 2012)

Yes, you are exactly right. However, the information in sheet "Summary" is populated by a pivot table and is refreshed periodically (with the associated fields populating via formula's)... so I can't safely assume that the data in the SKU column (A) of both sheet will match exactly. 

I think the code you sent gives me the groundwork I needed... I'll give it a shot. If you think I'm wasting my time after my response, please, by all means, tell me to stop what I'm doing


----------



## rywags11 (May 23, 2012)

I'm running into an interesting bug that I havent come across yet. When I try to refresh the data in "Summary", I keep getting "Run-time error '13' - Type mismatch". 

When I click "debug", it shows me that the "If Target <> "" Then" string is where its hitting a snag. It doesnt seem like its actually harming the function of the code though... can I just supress the error message?


----------



## AlbertMC2 (Jul 15, 2010)

Hi

Try replacing it with:

For the workbook:

```
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    
    If (Target.Column = 4) And (ActiveSheet.Name = "Summary") Then
        UserForm1.Show
    End If
    
End Sub
```
and for the population of the user form:

```
Private Sub UserForm_Activate()
    
    Dim ActiveR As Long
    Dim aCell As Range
    Dim aRow As Long
    
    ActiveR = ActiveCell.Row
    FindValue = Cells(ActiveR, 1).Value
    
    TextBox1.Value = Cells(ActiveR, 1).Value
    TextBox2.Value = Cells(ActiveR, 2).Value
    TextBox3.Value = Cells(ActiveR, 3).Value
    TextBox4.Value = Cells(ActiveR, 4).Value

    With Sheets("History1")
        Set aCell = .Range("A:A").Find(What:=FindValue, _
                          LookIn:=xlValues, _
                          LookAt:=xlWhole, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlNext, _
                          MatchCase:=False, _
                          SearchFormat:=False)
    
        If aCell Is Nothing Then
            MsgBox "SKU Value Not Found"
        Else
            aRow = aCell.Row
            TextBox5.Value = .Range("B" & aRow).Value
            TextBox6.Value = .Range("C" & aRow).Value
            TextBox7.Value = .Range("D" & aRow).Value
            TextBox8.Value = .Range("E" & aRow).Value
            TextBox9.Value = .Range("F" & aRow).Value
            TextBox10.Value = .Range("G" & aRow).Value
            TextBox11.Value = .Range("H" & aRow).Value
            TextBox12.Value = .Range("I" & aRow).Value
            TextBox13.Value = .Range("J" & aRow).Value
            TextBox14.Value = .Range("K" & aRow).Value
            TextBox15.Value = .Range("L" & aRow).Value
            TextBox16.Value = .Range("M" & aRow).Value
        End If
    End With
    
End Sub
```
Where *.Range("A:A")* is the range of the SKU column of your History sheet. This searches the whole of column A.
So if this range is only used from A1 to A30 then you can use *.Range("A1:A30")* which will then only search the first 30 cells in column A.


----------



## rywags11 (May 23, 2012)

Worked perfectly... Thank you again!

I sincerely appreciate your help today... I've learned a ton and you've been a great teacher!

And now that you may be sufficiently buttered up... do you know how I might be able to get a chart to show the trend of the values across the months... :facepalm:


----------



## AlbertMC2 (Jul 15, 2010)

Not sure exactly what you want but maybe see this article:
Excel - Add a Trendline to an Excel Chart to Forecast Results


----------



## rywags11 (May 23, 2012)

Sorry for not being a little more clear... I was too hasty in trying to be clever :smile:.

I'd like to put a line chart in the UserForm that illustrates the monthly data for the SKU it's showing you detali for.

For example:

SKU is 1234-567
Jan - April fields show 0 as the value
May - December show different values >0

The chart would show in the UserForm where I drop it, and the illustration would have the months in the X axis and the qty range in the Y axis, and the data points would then apply to their respective months.


----------



## AlbertMC2 (Jul 15, 2010)

Hi

See this article on how to put a chart on a User Form: Excel Developer Tip: Displaying a Chart in a UserForm

As an example:
Put a chart on your History Sheet. Format the chart how you want it using just one row of data. The chart could be situated out of the way.

Then add a picture placeholder object on you user form.

Your workbook SheetSelectionChange procedure changes like this:

```
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    Dim r As String
    
    If (ActiveSheet.Name = "Summary") And (Target.Column = 4) Then
        r = "A" & Target.Row & ":" & "M" & Target.Row
        Sheets("History1").ChartObjects(1).Chart.SetSourceData _
            Source:=Sheets("History1").Range(r), _
            PlotBy:=xlRows
        Set CurrentChart = Sheets("History1").ChartObjects(1).Chart
        Fname = ThisWorkbook.Path & "\temp.gif"
        CurrentChart.Export Filename:=Fname, FilterName:="GIF"
        UserForm1.Show
    End If
    
End Sub
```
The added code updates the chart source data with the cell data that you selected and then saves the chart as a picture (gif) file.

Your User form code changes like this:

```
Private Sub UserForm_Activate()
    Dim ActiveR As Long
    Dim aCell As Range
    Dim aRow As Long
    
    ActiveR = ActiveCell.Row
    FindValue = Cells(ActiveR, 1).Value
    
    TextBox1.Value = Cells(ActiveR, 1).Value
    TextBox2.Value = Cells(ActiveR, 2).Value
    TextBox3.Value = Cells(ActiveR, 3).Value
    TextBox4.Value = Cells(ActiveR, 4).Value

    With Sheets("History1")
        Set aCell = .Range("A:A").Find(What:=FindValue, _
                          LookIn:=xlValues, _
                          LookAt:=xlWhole, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlNext, _
                          MatchCase:=False, _
                          SearchFormat:=False)
    
        If aCell Is Nothing Then
            MsgBox "SKU Value Not Found"
        Else
            aRow = aCell.Row
            TextBox5.Value = .Range("B" & aRow).Value
            TextBox6.Value = .Range("C" & aRow).Value
            TextBox7.Value = .Range("D" & aRow).Value
            TextBox8.Value = .Range("E" & aRow).Value
            TextBox9.Value = .Range("F" & aRow).Value
            TextBox10.Value = .Range("G" & aRow).Value
            TextBox11.Value = .Range("H" & aRow).Value
            TextBox12.Value = .Range("I" & aRow).Value
            TextBox13.Value = .Range("J" & aRow).Value
            TextBox14.Value = .Range("K" & aRow).Value
            TextBox15.Value = .Range("L" & aRow).Value
            TextBox16.Value = .Range("M" & aRow).Value
        End If
    End With
    Image1.Picture = LoadPicture(ThisWorkbook.Path & "\temp.gif")
End Sub
```
The last line tells the image placeholder to load the chart gif image that was saved.

You can see this at work in the excel attachment.


----------

