submit data in visual basic

[Kashmala]

hi,
i have another problem, when a user hits the submit button data is submitted on an excel spreadsheet but its not stopping a user to submit the data only once.

how can i make my code to work in a way that if a user press submits button it shows a msg box informing user that the data has been submitted.

I have tried attaching msgbox buts its not working.

[Smartacus]

Well you are not telling us a lot.

1) Does your form save to a single row in the work sheet or to many worksheets?

2) What if the user did need to save it again because they found a typo? The rule would have to allow for that.

3) When they press the Submit button is it creating another row?

Easy way?

I suppose you could disable the button after pressing it until the next record but again that does leave the user not being able save a change if it takes them more than once to get it right.

When we do Insert or Updates in a DB we do If Exists, if true we update it, if false we create the record.

Come back and give more description about your submit button and data rows in your work sheet to get more help or suggestions.

[Kashmala]

Hi, thanks for the reply,

1) On pressing Submit button my form saves the results to a single row in the work sheer.

2) At the minute if a user enters data and press the Submit button it saves the data in the empty row on the worksheet but if I user thinks he has made an error and refills it and again press the submit button, instead of overwriting the submission it saves the data to next empty row.

This is the code which is attached to submit button.

Private Sub cmdSubmit_Click()
ActiveWorkbook.Sheets("sheet1").Activate
Range("A1").Select
Do
If IsEmpty(activecell) = False Then
activecell.Offset(1, 0).Select
End If
Loop Until IsEmpty(activecell) = True


activecell.Value = txtbox1.Text

activecell.Offset(0, 1) = cbDataDesc.Value

If chkBulk.Value Then
activecell.Offset(0, 2).Value = "B"
End If

If chkSensitive = True Then
activecell.Offset(0, 3).Value = "S"
End If

activecell.Offset(0, 4) = cboLocation.Value
activecell.Offset(0, 5) = txtSource.Value


ThisWorkbook.Save

End Sub

I really apreciate your help.
Thanks

[westconn1]

how do you refill to correct?

use a form level variable to keep the row number of the filled data
you can use this to save back to the same row
if new record set to 0 or next empty row

[Kashmala]

sorry being thick but i dont understand this.

[westconn1]

all right add another textbox to your form, can be invisible if you like
store the row number there

[Kashmala]

hi, tahnks for the reply, can you please incorportae this in to the code that i have provided here. If i create a text box then what declarations do i have to make? I understand the invisble one but how do i declare it to store the rows?

[westconn1]

as i have not seen the code you use to load data to the userform it is hard to show how to get the row number, but change the submit code like

Private Sub cmdSubmit_Click()
ActiveWorkbook.Sheets("sheet1").Activate
if hiddentext = 0 then hiddentext = range("a65335").end(xlUp).Row +1  ' next empty row
' if hidden textbox already has a value from when the record was loaded then it will overwrite the existing
cells(hiddentext,1) = txtbox1.Text
cells(hiddentext,2) = cbDataDesc.Value
 
If chkBulk.Value Then
cells(hiddentext,3).Value = "B"
End If
 
If chkSensitive = True Then
cells(hiddentext,4).Value = "S"
End If
 
cells(hiddentext,5) = cboLocation.Value
cells(hiddentext,6) = txtSource.Value
hiddentext = 0
ThisWorkbook.Save
End Sub