Excel UserForm Data View

[SeaMcD1]

I am trying to design a Userform that I can advance through existing spreadsheet data with Next and Previous command buttons. Data was originally entered into each cell of the spreadsheet. The spreadsheet has three column headings – “NAMES”, “ADDRESS”, and “PHONE”. The Userform has three corresponding textboxes (txt1, txt2, txt3).

How can I get the data in each cell of the spreadsheet to link to and appear in the corresponding textbox of the userform? Also, how can I get the previous/next buttons to advance/move back through the different rows of data?

 

[SkipVought]

Hi,

What code do you have and where are you stuck?

Hint...

userform1.textbox1.text = sheets("sheet1").cells(2,"A").value

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[combo]

You can change rowsource string:

Dim aRow As Long

Private Sub CommandButton1_Click()
aRow = aRow - 1
Call SetSource
End Sub

Private Sub CommandButton2_Click()
aRow = aRow + 1
Call SetSource
End Sub

Private Sub UserForm_Initialize()
aRow = 2
Call SetSource
End Sub

Private Sub SetSource()
With Me
    .TextBox1.ControlSource = Worksheets(1).Range("data").Cells(aRow, 1).Address
    .TextBox2.ControlSource = Worksheets(1).Range("data").Cells(aRow, 2).Address
End With
End Sub

combo

 

[SeaMcD1]

Thanks Combo!, I appreciate the leg-up on the code.

Ok, I'm getting a run-time error '1001': Application-defined or Object-defined error. The debugger is pointing to my Macro command below:

Sub Show_UserForm()
TestForm.Show
End Sub

I think the problem is in the SetSource code? Don't know if it was the right thing to do, but I replaced the "data" in each of the text boxes with column headings and changed the cell reference to 2, and 3 respectively. Should I be concerned about setting the ControlSource property of the textboxes. I am trying to get this, but I am stuck again.

 

[SkipVought]

Is your UserForm named TestForm?

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[rmikesmith]

Combo's "data" string referred to a named range. In this case I'm guessing it was meant to represent the range of actual information, names, addresses and phone numbers in your worksheet. You cannot substitute headings in your worksheet for this.


Regards,
Mike

 

[combo]

In addition to the above explanations:

When you refer to cells using ReferenceRange.Cells(x,y), x and y can point to cells that are outside the range, but still are within the worksheet, so you need to test if the row pointer points to a row within data range or first row.

If excel/windows date/number formats are different from US (used by VBA), you need either to work with strings or directly control data transferred to/from the userform.

combo

 

[SeaMcD1]

SkipVought - Yes, my UserForm name is TestForm.

rmikesmith - I understand about the data range comment and used the code as originally suggested.

combo - How can I test if the row pointer is pointing to a row in the spreadsheet?

I appreciate all the help, I'm really trying here. Any other suggestions?

 

[SkipVought]

"How can I test if the row pointer is pointing to a row in the spreadsheet?"

faq707-4594

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[combo]

Just test if aRow = aRow +/- 1 is valid action. You know where your data is, so you can test its size. Do not allow to point to the row outside data range (edit existing data) or more than the next empty row (new record mode).


combo

 

[SeaMcD1]

I solved the problem and this thread should be closed. I appreciate everyone's input and effort. Thanks.