Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Coding a Save button............
I need a little help in coding a, "Save" button on a VBA form. I have about 5 text boxes. The user enters data in each text box and, as of right now, the data automatically gets saved as I am using the _change event. (TextBox1_Change()) This stores the value of each text box into a cell of my choosing on an Excel spreadsheet automatically. From what I gather, it would be better, as a coder, to use the _Enter event. (TextBox1_Enter.) This way, when the user fills out the information in the text boxes, the information will NOT be loaded onto an Excel spreadsheet automatically as they type. (Which is what it does now by using the following code for each text box )Private Sub TextBox1_Change() Sheets("Sheet1").Range("A" & intRow.) = TextBox1.Value Instead, I want it to only be stored to the spreadsheet once they hit a, "Save" button. My question is this - simply put, how do I code the save button so that it stores all the information on all five text boxes? Also, note that the value of each text box gets stored in the next available (or empty) row in rows, "A", "b", "C", "D", and, "E." So the first time a user fills out a form, the values will go into cells, "A1", then in, "B1", then in, "C1", ect.......... The next time the user enters values in the text boxes (say if the form is cleared or the program is ran again), the value will be stored in, "A2", then in, "B2", then, "C2", ect....... (I think you get the picture.) When coding this, "Save" button, I want to make sure that if the user happens to NOT fill out a text box (which is allowable) when filling out a form that the next time they go to use the program and fill in the form, all values of all five text boxes are saved in that row. I have noticed that if the user DOES choose to fill in the text box that they skipped last time, that the value goes to the previous skpped instead of going to the CURRENT row. I hope I explained this enough for someone to understand cause I'm LOST with the coding of this. Please Help?!?! -- chadtastic |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Coding a Save button............
Chad,
Add a commandbutton to your userform, and use code like Private Sub CommandButton1_Click() Dim myRow As Long myRow = Worksheets("Sheet1").UsedRange.Cells( _ Worksheets("Sheet1").UsedRange.Cells.Count).Row + 1 Cells(myRow, 1).Value = Me.TextBox1.Text Cells(myRow, 2).Value = Me.TextBox2.Text Cells(myRow, 3).Value = Me.TextBox3.Text Cells(myRow, 4).Value = Me.TextBox4.Text Cells(myRow, 5).Value = Me.TextBox5.Text End Sub HTH, Bernie MS Excel MVP "chadtastic" wrote in message ... I need a little help in coding a, "Save" button on a VBA form. I have about 5 text boxes. The user enters data in each text box and, as of right now, the data automatically gets saved as I am using the _change event. (TextBox1_Change()) This stores the value of each text box into a cell of my choosing on an Excel spreadsheet automatically. From what I gather, it would be better, as a coder, to use the _Enter event. (TextBox1_Enter.) This way, when the user fills out the information in the text boxes, the information will NOT be loaded onto an Excel spreadsheet automatically as they type. (Which is what it does now by using the following code for each text box )Private Sub TextBox1_Change() Sheets("Sheet1").Range("A" & intRow.) = TextBox1.Value Instead, I want it to only be stored to the spreadsheet once they hit a, "Save" button. My question is this - simply put, how do I code the save button so that it stores all the information on all five text boxes? Also, note that the value of each text box gets stored in the next available (or empty) row in rows, "A", "b", "C", "D", and, "E." So the first time a user fills out a form, the values will go into cells, "A1", then in, "B1", then in, "C1", ect.......... The next time the user enters values in the text boxes (say if the form is cleared or the program is ran again), the value will be stored in, "A2", then in, "B2", then, "C2", ect....... (I think you get the picture.) When coding this, "Save" button, I want to make sure that if the user happens to NOT fill out a text box (which is allowable) when filling out a form that the next time they go to use the program and fill in the form, all values of all five text boxes are saved in that row. I have noticed that if the user DOES choose to fill in the text box that they skipped last time, that the value goes to the previous skpped instead of going to the CURRENT row. I hope I explained this enough for someone to understand cause I'm LOST with the coding of this. Please Help?!?! -- chadtastic |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Coding a Save button............
Can each of those textboxes/cells be empty? Or if the row is used, then at
least one certain cell/textbox has to have something in it? If each can be empty, and it doesn't matter which one is filled in (assumes that at least one textbox should have something in it before the Save button works, then I'd add another column (F??) that always gets populated when the record gets updated. I create a small userform with 5 textboxes (textbox1, ..., textbox5--names are important!) and two commandbuttons (Add and Cancel). This is the code behind that userform: Option Explicit Dim BlkProc As Boolean Private Sub CommandButton1_Click() Dim DestCell As Range Dim wks As Worksheet Dim iCtr As Long Set wks = Worksheets("myDataSheet") With wks Set DestCell = .Cells(.Rows.Count, "F").End(xlUp).Offset(1, 0) End With DestCell.Value = Application.UserName With DestCell.Offset(0, 1) .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With 'write the data For iCtr = 1 To 5 wks.Cells(DestCell.Row, iCtr).Value _ = Trim(Me.Controls("Textbox" & iCtr).Value) Next iCtr 'clear for next time???? BlkProc = True For iCtr = 1 To 5 Me.Controls("Textbox" & iCtr).Value = "" Next iCtr BlkProc = False Me.CommandButton1.Enabled = False End Sub Private Sub CommandButton2_Click() Unload Me End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) Call CommandButton2_Click End Sub Private Sub TextBox1_Change() If BlkProc = True Then Exit Sub Call CheckAddButton End Sub Private Sub TextBox2_Change() If BlkProc = True Then Exit Sub Call CheckAddButton End Sub Private Sub TextBox3_Change() If BlkProc = True Then Exit Sub Call CheckAddButton End Sub Private Sub TextBox4_Change() If BlkProc = True Then Exit Sub Call CheckAddButton End Sub Private Sub TextBox5_Change() If BlkProc = True Then Exit Sub Call CheckAddButton End Sub Private Sub UserForm_Initialize() With Me.CommandButton1 .Caption = "Add This Record" .Enabled = False End With With Me.CommandButton2 .Caption = "Cancel" .Cancel = True End With End Sub Private Sub CheckAddButton() Dim iCtr As Long Dim EnableAddButton As Boolean EnableAddButton = False For iCtr = 1 To 5 If Trim(Me.Controls("TextBox" & iCtr).Value) < "" Then EnableAddButton = True Exit For End If Next iCtr Me.CommandButton1.Enabled = EnableAddButton End Sub chadtastic wrote: I need a little help in coding a, "Save" button on a VBA form. I have about 5 text boxes. The user enters data in each text box and, as of right now, the data automatically gets saved as I am using the _change event. (TextBox1_Change()) This stores the value of each text box into a cell of my choosing on an Excel spreadsheet automatically. From what I gather, it would be better, as a coder, to use the _Enter event. (TextBox1_Enter.) This way, when the user fills out the information in the text boxes, the information will NOT be loaded onto an Excel spreadsheet automatically as they type. (Which is what it does now by using the following code for each text box )Private Sub TextBox1_Change() Sheets("Sheet1").Range("A" & intRow.) = TextBox1.Value Instead, I want it to only be stored to the spreadsheet once they hit a, "Save" button. My question is this - simply put, how do I code the save button so that it stores all the information on all five text boxes? Also, note that the value of each text box gets stored in the next available (or empty) row in rows, "A", "b", "C", "D", and, "E." So the first time a user fills out a form, the values will go into cells, "A1", then in, "B1", then in, "C1", ect.......... The next time the user enters values in the text boxes (say if the form is cleared or the program is ran again), the value will be stored in, "A2", then in, "B2", then, "C2", ect....... (I think you get the picture.) When coding this, "Save" button, I want to make sure that if the user happens to NOT fill out a text box (which is allowable) when filling out a form that the next time they go to use the program and fill in the form, all values of all five text boxes are saved in that row. I have noticed that if the user DOES choose to fill in the text box that they skipped last time, that the value goes to the previous skpped instead of going to the CURRENT row. I hope I explained this enough for someone to understand cause I'm LOST with the coding of this. Please Help?!?! -- chadtastic -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save button-shortcut | Excel Worksheet Functions | |||
Command Button Save As Application.ExecuteExcel4Macro ("SAVE.AS?() | Excel Discussion (Misc queries) | |||
how to get disk icon on save button of save as dialog like 2000 | Excel Discussion (Misc queries) | |||
save as isnt there neither is the save button pressable | Excel Discussion (Misc queries) | |||
Can't save Excel using Save button, | Excel Discussion (Misc queries) |