Coding a Save button............

[chadtastic]

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?!?!

 

[Bernie Deitrick]

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

 

[Dave Peterson]

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