Getting Data from Worksheet Using Userform

Question

I'm not a coding expert, and have become a little lost in what I'm trying to do. I have a spreadsheet, which has a number of columns to complete. This has become a little non-user friendly as you constantly need to scroll around the spreadsheet to add the data.

I've created a user form which will make the spreadsheet easier to update. This has been easier said than done. I have a Text Box, which the user states which line the retrieve the data, but the code seems to fail and I can't work out why.

The other thing I can't seem to find out is how to use this value to retrieve the data from the spreadsheet. The current code is shown below:

Private Sub UserForm_Initialize()

Dim xGateway As Range
Dim xCCode As Range
Dim xMIC As Range
Dim xCreator As Range
Dim xComplete As Range
Dim xData As Worksheet
Dim xComp As Worksheet
Dim xLine As String

   Set xData = Worksheets("Data")
   Set xComp = Worksheets("Tracker")

   xLine = TB_LineNo.Value

   For Each xGateway In xData.Range("Gateway")
   Me.CB_Gateway.AddItem xGateway.Value
   Next xGateway

   For Each xMIC In xData.Range("MIC")
   Me.CB_VGW.AddItem xMIC.Value
   Next xMIC

   For Each xCCode In xData.Range("Code")
   Me.CB_CCode.AddItem xCCode.Value
   Next xCCode

   For Each xCreator In xData.Range("Creator")
   Me.CB_Creator.AddItem xCreator.Value
   Next xCreator

   For Each xMIC In xData.Range("MIC")
   Me.CB_CBy.AddItem xMIC.Value
   Next xMIC

   For Each xMIC In xData.Range("MIC")
   Me.CB_ABy.AddItem xMIC.Value
   Next xMIC

   For Each xMIC In xData.Range("MIC")
   Me.CB_UpBy.AddItem xMIC.Value
   Next xMIC

   For Each xMIC In xData.Range("MIC")
   Me.CB_PubBy.AddItem xMIC.Value
   Next xMIC

   For Each xComplete In xData.Range("YN")
   Me.CB_Comp.AddItem xComplete.Value
   Next xComplete

   CB_Gateway.Value = xComp.Range(xLine, "k")
End Sub

The last line is the area where I'm having a problem, as it is failing and I'm not sure why. The xLine value, again I'm not sure if I have entered it correctly.

The next stage will be to amend the values where required and push the values back to the sreadsheet. I'm not sure how to do this as yet, whether it is better to do this from a command button etc.

Answer 1

Two issues here:

a) as BigBen wrote, you are mixing up two notations, both are valid, but parameter are different - in your case it's just a matter of taste which to use:

When using Range, the parameter is a range name as you would use it in an Excel formula: A1 for cell A1, or MIC for a named range. Assuming xLine holds the line number 10, you can concatenate the column K and the variable xLine giving you a string K10.
When using Cells, you have to pass row and column as separate parameters, so you would write either xComp.Cells(xLine, "K") or xComp.Cells(xLine, 11). Both the column number or column character(s) are valid as 2nd parameter.

b) you should ensure that xLine is a number, and that this number is valid, else you will face a runtime error. I would suggest you declare xLine as Long, convert the content of your textbox to a number and check if the number is greater than 0:

Dim xLine as long
xLine = Val(TB_LineNo.Value)
If xLine > 0 Then 
    CB_Gateway.Value = xComp.Cells(xLine, "K")
Else
    CB_Gateway.Value = "(invalid line)"
End If