Filling/referencing cells from User Form

X

Xhawk57

I have a worksheet with account numbers in column A and corecponding
information for those account numbers (customer name, type, balance, account
manager, etc) in each column. I have created a User Form in VB with text
boxes that correspond to the headings of blank columns that I have added to
the spreadsheet. what I would like to do is use the form to fill in the
information for each account number on the the sheet (the info would be typed
into the form and would fill the proper cell in the spreadsheet). I would
like this to happen for each account number, so as I complete the form for
one account number, it would move to the next account number's row and allow
me to enter the new information for that account number.

Any help on how to set up this form and link the information to the desired
cell would be greatly appreciated !
 
R

RyanH

On your Userform I would suggest having a Apply Button, Next Button, Back
Button and all the text boxes your application requires.

1.) Double click any cell in your data worksheet and all the information
for that account will load into the userform.

2.) Make changes then, Click the Apply Button to apply your changes

3.) Click the Next Button to go to the next Row

4.) Click the Back Button to go to the previous Row.

***Put this code in the worksheet module where your data is******

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Call LoadForm
UserForm1.Show
End Sub

**************************************************

****Put all of these procedures in your userform module*******

Private Sub btnApply_Click()

With UserForm1
Cells(ActiveCell.Row, "A").Value = .TextBox1
Cells(ActiveCell.Row, "B").Value = .TextBox2
Cells(ActiveCell.Row, "C").Value = .TextBox3
Cells(ActiveCell.Row, "A").Value = .TextBox4
Cells(ActiveCell.Row, "E").Value = .TextBox5
Cells(ActiveCell.Row, "F").Value = .TextBox6
Cells(ActiveCell.Row, "G").Value = .TextBox7
Cells(ActiveCell.Row, "H").Value = .TextBox8
End With

End Sub

Private Sub btnNext_Click()
ActiveCell.Offset(1).Select
Call LoadForm
End Sub

Private Sub btnBack_Click()
ActiveCell.Offset(-1).Select
Call LoadForm
End Sub

Private Sub LoadForm()

With UserForm1
.TextBox1 = Cells(ActiveCell.Row, "A").Value
.TextBox2 = Cells(ActiveCell.Row, "B").Value
.TextBox3 = Cells(ActiveCell.Row, "C").Value
.TextBox4 = Cells(ActiveCell.Row, "D").Value
.TextBox5 = Cells(ActiveCell.Row, "E").Value
.TextBox6 = Cells(ActiveCell.Row, "F").Value
.TextBox7 = Cells(ActiveCell.Row, "G").Value
.TextBox8 = Cells(ActiveCell.Row, "H").Value
End With

End Sub

*************************************************
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top