TextBox to display value in existing cell then update it

**vceh** · 07-13-2011, 03:02 PM

Hello!

I've been trying to figure this out for a couple of days- no luck. I built a UserForm that populates an Excel sheet based on some simple choices and data entry.

First, the user must choose 2 values from 2 dropdown menus:
1. Month
2. Facility

Then they are to enter 2 numbers into 2 text boxes: TextBox1 and TextBox2. These numbers are recorded on the spreadsheet in columns D and E, respectively. The target row depends on the Month and Facility selected.

Now here's the problem: When the user initializes the UserForm, then picks Month and Facility, I'd like TextBox1 to display the current value in the corresponding D cell. The user can then choose to keep the shown value or update it then hit Enter to populate the spreadsheet.

I'm loosing my mind over this! I feel like it's probably a very simple one-line code, but for some reason I just can't get it right!

Thank you!

**barryleajo** · 07-13-2011, 03:08 PM

Hi vceh
Welcome to the forum.
Upload your example for us to work on
It will save us having to reinvent a wheel before we can address your requirement

**vceh** · 07-13-2011, 03:59 PM

Hi Barry! Thank you

It's for work and for regulatory purposes, so I'm not sure it would be OK to upload the whole thing. Contains company-specific data. I can maybe post a snapshot of the UserForm or a section of code. Here's some of it:

Private Sub UserForm_Initialize()
    Dim cMonth As Range
    Dim cFacility As Range
    Dim ws As Worksheet
    Set ws = Worksheets("Jan")

    For Each cMonth In ws.Range("Months")
    With Me.cboMonth
        .AddItem cMonth.Value
        .List(.ListCount - 1, 1) = cMonth.Offset(0, 1).Value
    End With
    Next cMonth

    For Each cFacility In ws.Range("Facility_1")
    With Me.cboFacility
        .AddItem cFacility.Value
    End With
    Next cFacility

    
    Me.TextBox2.Value = 0
    
    Me.cboMonth.SetFocus

Now, you can see TextBox2 defaults to 0, which is fine. I'd like for TextBox1 to default to a value within a cell, depending on the Month and Facility selected.

**barryleajo** · 07-13-2011, 05:22 PM

vceh
The attached bare bones idea may get you going - macro "uf".
Select from the comboboxes and click anywhere on the user form.
Change or accept Texbox1 and click OK to write it back.
Basically it uses the ListIndex property of the comboboxes to identify a cell to populate Textbox1.
You can then develop it to suit
barry

**vceh** · 07-14-2011, 10:08 AM

Thanks for your help, Barry!

I only used the UserForm_Click() section, and I keep getting this error:

Run-time error ‘91’:
Object variable or With block variable not set

Can you explain it to me? I'm not sure what it's asking for here. It was giving me a different error, but when I declared ws As Worksheet within the Sub, it started giving me this one.

Sub UserForm_Click()

    Dim ws As Worksheet
    
    Me.TextBox1.Value = ws.Range("D4").Offset(Me.cboMonth.ListIndex, Me.cboFacility.ListIndex)


End Sub

**vceh** · 07-14-2011, 01:07 PM

Figured it out: I should've used Set ws = Worksheets("Jan") instead of Dim ws As Worksheet. I'm new to all of this, so that explains the silly mistake.

Thanks, again, Barry!

**barryleajo** · 07-14-2011, 03:19 PM

Well done mate!
Glad to be of help.
If you are satisfied then please mark thread as Solved.

**bittu2010** · 01-07-2012, 09:22 PM

Hi Guys ,

I need a excel sheet where if I click on a single cell value ( Cell A1) in a range then its value should show in a msg box ... please help me ..