User Form Help - VBA

[AG0607]

Hi all,

Just wondering if anybody can help. I'm currently working on a project which involves filling in a user form which will then transfer the data in-putted onto a worksheet.

At some point each entry will need to be updated, which again is done through the user form. You will input your reference number, it will automatically populate the user form with the details you have previously submitted, you then change the necessary fields, press update and the worksheet will update itself.

The only problem I am having is that after bringing up an auto populated form, when I press submit it will submit as a completely new entry, rather than over riding the cells I need it to with new data and I can't figure out how to get around it.

The code I am using at the moment is

Private Sub cmdSubmit_Click()
'Count for next available row on main log sheet

If (lblStts.Caption = "Adding") Then
Dim ws As Worksheet
Set ws = Worksheets("Log Sheet")

Dim Count As String
Count = "1234"
counter = 8

Do While Count = "1234"
If ws.Cells(counter, 2).Value <> "" Then counter = counter + 1
If ws.Cells(counter, 2).Value = "" Then Count = "Infinity"
Loop

'Input form values into following location
ws.Cells(counter, 2).Value = txtTitle.Value
ws.Cells(counter, 3).Value = cmbType.Value
ws.Cells(counter, 4).Value = txtDate.Value
ws.Cells(counter, 5).Value = txtReqBy.Value
ws.Cells(counter, 6).Value = txtReqFor.Value
ws.Cells(counter, 7).Value = Environ("USERNAME")
ws.Cells(counter, 8).Value = Format(Now)
ws.Cells(counter, 9).Value = ("SDR" & counter - 7)
ws.Cells(counter, 10).Value = txtDetails.Value
ws.Cells(counter, 11).Value = txtDue.Value
ws.Cells(counter, 12).Value = txtUpdate.Value
ws.Cells(counter, 13).Value = cmbStatus.Value
If cmbStatus.Value = "Closed" Then
ws.Cells(counter, 14).Value = Environ("USERNAME")
ws.Cells(counter, 15).Value = Format(Now)
End If
MsgBox ("Your entry has been submitted - Your ticket number: SDR" & counter - 7)

ElseIf (lblStts.Caption = "Updating") Then
'update
    Range("'Log Sheet'!B" & updaterow) = txtTitle.Value
    Range("'Log Sheet'!C" & updaterow) = cmbType.Value
    Range("'Log Sheet'!D" & updaterow) = txtDate.Value
    Range("'Log Sheet'!E" & updaterow) = txtReqBy.Value
    Range("'Log Sheet'!F" & updaterow) = txtReqFor.Value
    Range("'Log Sheet'!G" & updaterow) = Environ("USERNAME")
    Range("'Log Sheet'!I" & updaterow) = ("SDR" & counter - 7)
    Range("'Log Sheet'!J" & updaterow) = txtDetails.Value
    Range("'Log Sheet'!K" & updaterow) = txtDue.Value
    Range("'Log Sheet'!L" & updaterow) = Environ("USERNAME")
    If cmbStatus.Value = "Closed" Then
    ws.Cells(counter, 14).Value = Environ("USERNAME")
    ws.Cells(counter, 15).Value = Format(Now)
    End If
Else
MsgBox ("Something went wrong")
End If

Unload Me

End Sub
'Clear and cancel entry
Private Sub cmdCancel_Click()
Unload Me
SDLog.Hide
End Sub

I'm not sure if that makes perfect sense, but any help or suggestions will be greatly appreciated.

Many thanks,

Andy.

 

[Jerry Sullivan]

Hi Andy,

It looks like the only thing that is different between a new record and an update is the row, for which you are using the variable "counter".

One approach would be to use a worksheet cell (C1 in the example below) to store the Row number of the record you are editing. Whatever code you have that loads the record into the form should set C1 to the Row Number. What ever code starts a new record should write "Next Row" or 0 into C1.

Then your code could use something like this to set your variable counter.

Select Case Range("C1")
    Case "Next Row" 
        counter = Range("B" & Rows.Count).End(xlUp).Row + 1
    Case Is > 0
        counter = Range("C1")
    Case Else
        MsgBox "Error"
End Select

'Input form values into following location
ws.Cells(counter, 2).Value = txtTitle.Value
ws.Cells(counter, 3).Value = cmbType.Value
'...your code continues

With this approach you could eliminate the second block of code that you were trying to use for updates.