Populating Excel Data into a Userform

[Kari2010]

I would appreciate any help that anyone can offer. Please note (disclaimer ) I am new to using forms and to VBA coding in general.

I have to develop a form which will ultimately populate several text boxes from an excel worksheet. In order to test my ability to do this, I made a 'test' excel spreasheet with five columns of data (CustomerID, Name, City, State, and ZipCode).

I have Command buttons Previous, Next, First, and Last to manuever through the rows of data. I also have a RowNumber textbox to dispay the rows (I have text set to 2). I have an Add command button, as well as Save and Close.

I have basically copied code from elsewhere to do this, but I cannot get it to work. I get multiple error messages, including that 'ClearData' is not defined. Can someone take a look at this code and possibly tell me what could be going wrong? I have checked and double-checked that my Column and text box names match.

When I run this it does not pull in the data and i cannot navigate or enter any row number without getting an 'invalid row' message.

Thank you in advance for any help!

Here is the GetData routine:

Private Sub GetData()

Dim r As Long

If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)

Else
ClearData
MsgBox "Illegal row number"
Exit Sub

End If

If r > 1 And r <= LastRow Then
CustomerId.Text = FormatNumber(Cells(r, 1), 0)
CustomerName.Text = Cells(r, 2)
City.Text = Cells(r, 3)
State.Text = Cells(r, 4)
Zip.Text = Cells(r, 5)

DisableSave

ElseIf r = 1 Then
ClearData

Else
ClearData
MsgBox "Invalid row number"

End If

End Sub

Private Sub ClearData()

CustomerId.Text = ""
CustomerName.Text = ""
City.Text = ""
State.Text = "AK"
Zip.Text = ""

End Sub

Private Sub DisableSave()

CommandButton5.Enabled = False
CommandButton6.Enabled = False

End Sub

Private Sub RowNumber_Change()

GetData

End Sub
Private Sub CommandButton2_Click()

Dim r As Long

If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)

r = r ? 1
If r > 1 And r <= LastRow Then
RowNumber.Text = FormatNumber(r, 0)

End If

End If

End Sub

 

[SkipVought]

hi,

What is the purpose of the UserForm?

What are the Controls on the UserForm?

Have you looked at the Data > Form... feature of native Excel?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Kari2010]

Hi - thanks for your reply. I'm not sure what you mean by "native" excel.

The controls are all text boxes.

The purpose of the user form ultimately will be to facilitate some data collection during an interview with about 20 individuals, for which some demographic data will already be present and viewable (and editable, hopefully) and there will be text boxes to allow for data entry during the interview, which will then be added.

 

[SkipVought]

Native Excel is Excel as designed and delivered.

It has a built-in Form to read/populate data in in a table in a sheet. NO VBA REQUIRED!

Data > Form...

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Kari2010]

I can't find Data>Form (I am, unfortunately from what I have learned, using Excel 2007).

I hope I can figure out how to find the form. "No VBA required" sounds great right about now...

 

[SkipVought]

Customize your QAT, and look for Form in ALL COMMANDS

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

You can also get the form by,,,

1) select in your table

2) alt D O

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Kari2010]

Thanks so much. I found the form. However this isn't going to work for my fields, as I can't figure out a way to change the height of the fields (much of my information in any given field is several sentences long, and I need it to be able to display on the form).