Application Worksheet

[excelenergy]

I'm trying to get a text box on a user form to look up a value in a combobox on the user form...then populate itself with the value it finds.


I'm trying to use Application.Worksheet

 Private Sub TextBox1_Change()
With Me
.Reg1 = Application.WorksheetFunction.VLookup(CLng(Me.Reg1), Sheet8.Range("A:E"), 2, 0)
End With
End Sub

Doesn't seem to work. I'm not getting any errors. Just no result in the textbox. Any ideas of a more useful way to do this?

 

[Fluff]

At the moment that code is looking up the value in Reg1 & then trying to populate itself.
should it be

 Private Sub TextBox1_Change()
With Me
.TextBox1.Value = Application.WorksheetFunction.VLookup(CLng(.Reg1.Value), Sheet8.Range("A:E"), 2, 0)
End With
End Sub

 

[excelenergy]

Hey Fluff,

Thanks for the response. I tried that out. I'm getting an "Unexpected separator error" - I don't think VBE likes the name of my sheet...Its called like Dynamic Drop Down (4)....when vbe errors, that's what its highlighting, the name of the sheet I'm referencing.

At the moment that code is looking up the value in Reg1 & then trying to populate itself.
should it be

 Private Sub TextBox1_Change()
With Me
.TextBox1.Value = Application.WorksheetFunction.VLookup(CLng(.Reg1.Value), Sheet8.Range("A:E"), 2, 0)
End With
End Sub

 

[Fluff]

Then replace Sheet8 with the name of your sheet

 

[excelenergy]

This is it...Its still saying unexpected separator

.TextBox1.Value = Application.WorksheetFunction.VLookup(CLng(.Dynamic Chart Drop Down 00 (2).Range.Value), Dynamic Chart Drop Down 00 (2).Range("A:E"), 2, 0)

Then replace Sheet8 with the name of your sheet

 

[Fluff]

Try

Private Sub TextBox1_Change()
With Me
.TextBox1.Value = Application.WorksheetFunction.VLookup(CLng(.Reg1.Value), Sheets("Dynamic Chart Drop Down 00 (2)").Range("A:E"), 2, 0)
End With
End Sub

 

[excelenergy]

Hey Fluff, sorta thing as originally. No errors this time.

The textbox just isn't populating itself that's all. But as far as errors go, nothing popped up.

 

[Fluff]

What is the value in Reg1?

 

[Norie]

Try putting the code in the Change event of the combobox rather than the textbox you are trying to populate.

 

[excelenergy]

Hey guys,

I rearranged it to the change event.

Error on this line - Script out of range.
With ThisWorkbook.Sheets(Sheet8)

Private Sub ComboBox3_Change()
 Dim Sheet8 As String
    sheetName = "Sheet8"
    With ThisWorkbook.Sheets(Sheet8)
       'For each name in your range
        For Each cell In .Range("A15:J30000")
           'If the name is the one selected in your combobox
            If (cell = ComboBox1.Text) Then
                'Increment value
                .Range(cell.Address).Offset(0, 1).Value = _
                .Range(cell.Address).Offset(0, 1).Value + TextBox1.Value
            End If
        Next cell
    End With
End Sub

Try putting the code in the Change event of the combobox rather than the textbox you are trying to populate.