Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Hello,
I am not sure of how to do this, nor if it is possible. I currently have a form, it has 4 comboboxes, 1 textbox, 3 cmdbuttons. This works great now, but would like to modify to be better. The form is used to enter information into the row, but there must be a blank row with the formulas already there for it to insert into, this is the current code from the form: Option Explicit Private Sub cboProduct_Change() Dim SourceData As Range Dim val1 As String Set SourceData = Range(cboProduct.RowSource) val1 = cboProduct.Value lblProduct = val1 End Sub Private Sub cboBankID_Change() Dim SourceData As Range Dim val1, val2 As String Set SourceData = Range(cboBankID.RowSource) val1 = cboBankID.Value val2 = SourceData.Offset(cboBankID.ListIndex, 1).Resize(1, 1).Value If cboBankID.Value = "TESTME" Then lblBankID = val1 lblProject = "" Else lblBankID = val1 lblProject = val2 End If End Sub Private Sub cboProject_Change() Dim SourceData As Range Dim val1 As String Set SourceData = Range(cboProject.RowSource) val1 = SourceData.Offset(cboProject.ListIndex, 1).Resize(1, 1).Value lblProject = val1 End Sub Private Sub cboServType_Change() Dim SourceData As Range Dim val1 As String Set SourceData = Range(cboServType.RowSource) val1 = SourceData.Offset(cboServType.ListIndex, 1).Resize(1, 1).Value lblServType = val1 End Sub Private Sub cmdCancel_Click() Unload Me End Sub Private Sub cmdClearForm_Click() Call UserForm_Initialize End Sub Private Sub cmdInsert_Click() ActiveSheet.Activate Range("A8").Select Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True ActiveCell.Value = lblProduct ActiveCell.Offset(0, 1) = lblBankID ActiveCell.Offset(0, 2) = lblProject ActiveCell.Offset(0, 3) = lblServType ActiveCell.Offset(0, 8) = txtAmount With ActiveCell.Offset(0, 8) .Value = .Value * 1 .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)" End With End Sub Private Sub cmdSelect_Click() Application.CommandBars("Workbook tabs").ShowPopup 500, 200 End Sub Private Sub UserForm_Initialize() txtAmount.Value = "" cboProduct.Value = "" lblProduct = "" cboBankID.Value = "" lblBankID = "" cboProject.Value = "" lblProject = "" cboServType.Value = "" lblServType = "" cboProduct.SetFocus End Sub What i would like: insead of inserting just one row at a time..... I would like to take the 5 user entries, store them, then, when all rows that are needed for the sheeet, add the rows to the proper sheet. The rows in the sheet have formula's, so i would need to copy the row above first, then, i want to store that info into ???? [what should i store into??? listbox? ]....then, add the info, if i needed another row, i would select the add button (not currently used yet), then when all info is collected, insert into the sheet. In other words, before i hit [insert], i want to gather the info first, it might be 5 lines of information, maybe one...but include a button that 'adds' another line, then when all lines or rows are needed and ready, insert it into the worksheet... I hope that i have explained this properly. Hope that someone can help me find the solution. In advance, thanks for any help provided. K~ |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
User form to sort data | Excel Programming | |||
displaying the user form data | Excel Programming | |||
user form - data range | Excel Programming | |||
Data from user form to certain cell | Excel Programming | |||
Reading Data from a User Form. | Excel Programming |