VBA - Compile Error using x1UP

[manny88]

Hi,

I'm trying to save a range using an activeX button but the VB code I am using is giving me an error message saying the variable is not defined.

Can anyone help?

Private Sub cmdSave_Click()

    Application.ScreenUpdating = False
        Dim iRow As Long
        iRow = Sheets("Admin").Range("B1048576").End(x1up).Row + 1
    
    If validationForm = True Then
        With ThisWorkbook.Sheets("Admin")
            .Range("B" & iRow).Value = iRow - 1
            .Range("C" & iRow).Value = txtRefNumber.Value
            .Range("D" & iRow).Value = txtDateOutcome.Value
            .Range("E" & iRow).Value = txtDateShelf.Value
            .Range("F" & iRow).Value = txtDM.Value
            .Range("G" & iRow).Value = cmbServedBy.Text
            .Range("H" & iRow).Value = txtDateServed.Value
            
    End With
        Call Reset
    Else
        Application.ScreenUpdating = True
        
        Exit Sub
    
     End If
        Application.ScreenUpdating = True
        
End Sub

 

[DanteAmor]

In this line you have 1 (one)

iRow = Sheets("Admin").Range("B1048576").End(x1up).Row + 1

It must be the letter ( l )
iRow = Sheets("Admin").Range("B1048576").End(xlup).Row + 1

 

[manny88]

In this line you have 1 (one)

iRow = Sheets("Admin").Range("B1048576").End(x1up).Row + 1

It must be the letter ( l )
iRow = Sheets("Admin").Range("B1048576").End(xlup).Row + 1

Thanks. The error has gone, but the function of the button is not doing what I intended - it's doing nothing.

It's a user form with a Save button on my main sheet titled "Form". Clicking the Save button should do 3 things; input the entries on the form into the sheet "Admin" in corresponding columns; save the document; provide a msgbox asking "View your entries" and "Exit", which would close the document. View entries should only show entries for the windows user.

Date Outcome and Date placed on Shelf should auto-populate with current day's date but should allow to the edit field also.

I wasn't sure how to auto-populate the owner field with Outlook/Windows name - it's not completely necessary but helps towards the integrity of the data. This would go hand-in-hand with the "View" button that will show all entries from the Windows user.

I am ignoring Published by and Served Date as I do not need it right now.

 

[DanteAmor]

Try this to save.

Private Sub cmdSave_Click()
  Dim lr As Long
  If txtRefNumber = "" Then
    MsgBox "Fill txtRefNumber"
    txtRefNumber.SetFocus
    Exit Sub
  End If
  With ThisWorkbook.Sheets("Admin")
    lr = Sheets("Admin").Range("B" & Rows.Count).End(xlUp).Row + 1
    .Range("B" & lr).Value = txtRefNumber.Value
    .Range("C" & lr).Value = Date   'txtDateOutcome.Value
    .Range("D" & lr).Value = Date   'txtDateShelf.Value
    .Range("E" & lr).Value = Application.UserName 'txtDM.Value
    .Range("F" & lr).Value = cmbServedBy.Text
    .Range("G" & lr).Value = txtDateServed.Value
  End With
  Call reset
End Sub

 

[manny88]

Try this to save.

Private Sub cmdSave_Click()
  Dim lr As Long
  If txtRefNumber = "" Then
    MsgBox "Fill txtRefNumber"
    txtRefNumber.SetFocus
    Exit Sub
  End If
  With ThisWorkbook.Sheets("Admin")
    lr = Sheets("Admin").Range("B" & Rows.Count).End(xlUp).Row + 1
    .Range("B" & lr).Value = txtRefNumber.Value
    .Range("C" & lr).Value = Date   'txtDateOutcome.Value
    .Range("D" & lr).Value = Date   'txtDateShelf.Value
    .Range("E" & lr).Value = Application.UserName 'txtDM.Value
    .Range("F" & lr).Value = cmbServedBy.Text
    .Range("G" & lr).Value = txtDateServed.Value
  End With
  Call reset
End Sub

Ah! that almost did it!
It just doesn't allow me edit the date in the message box to an alternative date i.e. picture attached. My goal was to provide the current day as the default which should autofill oncedocument is opened - already in the message box, but also allow a previous date to be entered. Right now it is only adding the current day's date regardless of what I enter.

The owner field should also be the same and should already include the Windows name in the message box once the document is opened.7

 

[DanteAmor]

I don't know if I'm understanding your requirement well.
The userform is divided into several stages (so to speak).
1. Start
2. Capture
3. Pass the data to the sheet.

Your syntax error is part 3, pass the data to the sheet.

But now I think you're talking about part 1, start of the userform.

Try this code in your userform:

Private Sub cmdSave_Click()
  Dim lr As Long
  If txtRefNumber = "" Then
    MsgBox "Fill txtRefNumber"
    txtRefNumber.SetFocus
    Exit Sub
  End If
  With ThisWorkbook.Sheets("Admin")
    lr = Sheets("Admin").Range("B" & Rows.Count).End(xlUp).Row + 1
    .Range("B" & lr).Value = txtRefNumber.Value
    .Range("C" & lr).Value = txtDateOutcome.Value
    .Range("D" & lr).Value = txtDateShelf.Value
    .Range("E" & lr).Value = txtDM.Value
    .Range("F" & lr).Value = cmbServedBy.Text
    .Range("G" & lr).Value = txtDateServed.Value
  End With
  Call reset
End Sub

Private Sub UserForm_Activate()
  txtDateOutcome.Value = Date
  txtDateShelf.Value = Date
  txtDM.Value = Application.UserName
End Sub