Combobox to populate Textbox on Userform - Error 1004

markw1804

New Member
Joined
Nov 19, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hi,

I am trying to put together a real simple Userform for a Excel spreadsheet that basically holds scores, I have a combobox and 10 other text boxes on a userform . The combobox is pulling the player names then all I want is for the name selected to then populate the text boxes with the information from the information in the corresponding columns C to L right of the PlayerName on the worksheet, the scores can then be reviewed and updated before posting back at the moment all I am trying to do is get the first box to populate then go from there.

When I select the name I am getting Runtime Error 1004 - Method 'Range' of runtime object '_Worksheet' failed.
I have searched and found many suggestions on this error and tried several things changing all the cells to text and changed from Application.Worksheet.VLookup to how it looks now? Same 1004 error at same point.
I am sure its must be obvious but I cant seem to see the wood for the trees?

All the data on the worksheet is incorporated in a table - Table1, The Player Name is pulled from Column A which is marked as a Table - Player

VBA Code:
Private Sub UserForm_Initialise()
cmbPlayerName.List = Worksheets("Scores").Range("Player").Value
End Sub


Private Sub cmbPlayerName_Change()
Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Scores")

With Me
        .txtHcp = Application.VLookup(Me.cmbPlayerName, ws.Range("Table1"), 3, False)
    End With

End Sub


This the line the debugger is taking me to, and I suspect its the ws.Range("Table1") thats my issue but I cant get my head round the issue

Any help would be very much appreciated

Thanks
Mark
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

markw1804

New Member
Joined
Nov 19, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
would actually be Application.WorksheetFunction.VLookup.

Unless you have the actual range named as Table1, this would not work

You could also just use the range address
VBA Code:
ws.Range("A1:C1000"),
Thanks Dave, Its working now, much appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,405
Messages
5,624,574
Members
416,036
Latest member
eloisa manzanarez

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top