PDA

View Full Version : Put Data On One Row in Excel With Many Forms



rupesh
09-24-2013, 11:44 AM
I have one userform on that form have 10 combo boxes, Text boxes, and 10 check boxes, each check boxes check event have userforms with Combo boxes and 3 button name is Ok, next and previous. Ok for enter record to excel sheet, Next for next record and previous for previous record, My problem is when I press ok button on each form value inserted in new line, as i said I have 1 main form and 10 forms on checkbox check event total 11 forms. for one record it take 11 rows.

How do I add all form input data into one row. also how do i set next value on Next button , previous value on Previous button.

So if any buddy have solution on this problem, please help

SamT
09-24-2013, 04:00 PM
Rupesh,

Hi, and Welcome to VBA Express.

I moved your thread to the Excel Forum because it is a better place to seek help on this issue. Then I changed the Title of the thread to better indicate what the issue is.


For the Row number, I use a Label or TextBox with Visible set to False or Hidden set to True. I put the control on the main form, Name it CurrentRow, and set it's text or Caption as needed. Then any Form can use it by
Rw = MainForm.CurrentRow

Two more Form Tricks:

1)I put the relevant Column number or letter in each Control's Tag.

2) I Make a User Defined Collection named DataCtrls and Add each Control that is a Record Field Control.


Sub SaveRecord()
For Each Ctrl in DataCtrls
Sheets("Sheet1").Cells(Rw, Ctrl.Tag) = Ctrl.Value
Next Ctrl

rupesh
09-24-2013, 09:29 PM
I have excel sheet which having 389 column for this sheet have 17 userforms first form is main form, I call this 16 forms on the 16 check boxes check event on first form. I have problem to set Next row value to "Next" Command button and same for previous button. Please help me if any buddy have solution.

SamT
09-25-2013, 08:54 AM
Rupesh, Read the Forum FAQS and Rules. (http://www.vbaexpress.com/forum/faq.php)


I moved your first question to the better Forum Folder as a courtesy to you and you did not even ackowledge the favor I did you. Then you posted a new thread with a follow up question to the first one.

The followup question should have been in the original thread. So as a courtesy to you, I moved both questions into the same thread.

If you continue to show such rudeness to us, no one will help you.

Please, read the Forum FAQS and Rules. (http://www.vbaexpress.com/forum/faq.php)

SamT
09-25-2013, 09:15 AM
All this Code goes on the Main Form's code page.


Function NextRow() As Long
'How it Works:
'The Range "Cells(Row, Column)" Return a single cell on the Sheet.
' "Rows.Count" is the Last Row on the Sheet.
' "End(xlUp)" Returns the first (Empty/Non-Empty) Cell above the Cell the "End" Starts from.
'This is like selecting a cell and pressing the Up Arrow on the keyboard.

Const Sht = "Sheet1" 'Set this Constant to the Sheet Name that is used
Const Col = "A" 'Set this Constant to a column that always has a value in the record field

NextRow = Sheets(Sht).Cells(Rows.Count, Col).End(xlUp). Row + 1
End Function


Function RecordRow(RecordID) As Long
Const Sht = "Sheet1" 'Set this Constant to the Sheet Name that is used
Const Col = "A:A" ' Set this Constant to the column that contains the Record ID

RecordRow = Sheets(Sht).Range(Col).Find(RecordID).Row
End Function



Example of Next Button Click
Private Sub Next_Click()
NextRowValue = NextRow
End Sub

Example of Code for Previous button click
Private Sub Previous_Click(0
PeviousRow = NextRowValue - 1

Example of code to call RecordRow


Dim X As Long
X = RecordRow(ComboBox1.Value) 'Where ComboBox1 is the list of Record ID's and the Value is the Selected ID

Rupesh,

if the above is not enough to help you solve the problems, please use the "Go Advanced" Reply button and at the bottom of the Advanced Reply page use the "Manage Attachments" button to upload a sample of your workbook.

Please be sure to delete all but 4 or 5 rows on the database sheet, and to confuse any personal or proprietary data in it.