VBA code stopped working, can't figure out what went wrong

dlmoore99

Board Regular
Joined
May 20, 2011
Messages
88
Office Version
  1. 2019
Platform
  1. MacOS
I have a userform that I've been developing for a few weeks now. My Private Sub cmdADD_Click() button will not operate anymore and debugger window appears when clicked. When I try to debug it goes to this line of code: .Cells(iRow, 1).Value = Me.txtDate.Value. Button was working great, but then stopped and I can't figure out why. Can someone please assist. Thanks in advance. Here is the code

Code:
Private Sub cmdADD_Click()
Dim iRow As Long
Dim LstRw As Long
Dim ws As Worksheet
Set ws = Worksheets("TGT")

iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
If Trim(Me.txtTime.Value) = "" Then
  Me.txtTime.SetFocus
  MsgBox "Please enter complete Target data"
  Exit Sub

End If

With ws
[COLOR=#ff0000]  .Cells(iRow, 1).Value = Me.txtDate.Value[/COLOR]
  .Cells(iRow, 2).Value = Me.txtTime.Value
  .Cells(iRow, 3).Value = Me.txtZulu.Value
  .Cells(iRow, 5).Value = Me.cboTGTBlock.Value & Me.txtTGTNo.Value
  .Cells(iRow, 6).Value = Me.cboPOO_GridZone.Value & Me.cboPOO_100K.Value & Me.txtPOO_East.Value & Me.txtPOO_North.Value
  .Cells(iRow, 7).Value = Me.txtPOO_Alt.Value
  .Cells(iRow, 8).Value = Me.cboPOI_GridZone.Value & Me.cboPOI_100K.Value & Me.txtPOI_East.Value & Me.txtPOI_North.Value
  .Cells(iRow, 9).Value = Me.txtPOI_Alt.Value
  .Cells(iRow, 10).Value = Me.txtRange.Value
  .Cells(iRow, 11).Value = Me.txtRCS.Value
  .Cells(iRow, 12).Value = Me.txtVel.Value
  .Cells(iRow, 13).Value = Me.cboType.Value
  .Cells(iRow, 14).Value = Me.txtComments.Value
  .Cells(iRow, 16).Value = Me.cboZulu.Value

End With
'clear the data
Me.txtTime.Value = ""
Me.txtZulu.Value = ""
Me.txtTGTNo.Value = Me.txtTGTNo.Value + 1
Me.txtPOO_East.Value = ""
Me.txtPOO_North.Value = ""
Me.txtPOO_Alt.Value = ""
Me.txtPOI_East.Value = ""
Me.txtPOI_North.Value = ""
Me.txtPOI_Alt.Value = ""
Me.txtRange.Value = ""
Me.txtRCS.Value = ""
Me.txtVel.Value = ""
Me.cboType.Value = ""
Me.txtComments.Value = ""
Me.txtTime.SetFocus
End Sub

Private Sub cmdCancel_Click()
Application.Visible = True
Me.Hide
End Sub
Private Sub cmdClear_Click()
Dim answer As VbMsgBoxResult
    answer = MsgBox("Are you sure you want to clear Target Data?", vbYesNo, "Clear Target Data")
    If answer = vbYes Then
        Me.txtTime.Value = ""
        Me.cboZulu.Value = ""
        Me.txtZulu.Value = ""
        Me.txtPOO_East.Value = ""
        Me.txtPOO_North.Value = ""
        Me.txtPOO_Alt.Value = ""
        Me.txtPOI_East.Value = ""
        Me.txtPOI_North.Value = ""
        Me.txtPOI_Alt.Value = ""
        Me.txtRange.Value = ""
        Me.txtRCS.Value = ""
        Me.txtVel.Value = ""
        Me.cboType.Value = ""
        Me.txtComments.Value = ""
    End If
    
End Sub
Private Sub cmdShowWb_Click()
Application.Visible = True
Me.Hide
End Sub

Private Sub Image1_Click()
End Sub

Private Sub Label2_Click()
End Sub

Private Sub Label5_Click()
End Sub

Private Sub ListBox1_Click()
End Sub

Private Sub UserForm_Initialize()
txtDate.Value = Format(Date, "mm/dd/yyyy")
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Cancel = True
    'use a button to close user form and not the windows "X"
    MsgBox "Please use the Cancel Target button to close the form.", , "Close Form"
End Sub

Private Sub txtRCS_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim dblNum As Double
    If IsNumeric(Me.txtRCS.Value) Then
        dblNum = Me.txtRCS.Value
        Me.txtRCS.Value = Format(dblNum, "-00")
    End If
End Sub

Private Sub txtTime_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    
    Dim dblNum As Double
    If IsNumeric(Me.txtTime.Value) Then
       dblNum = Me.txtTime.Value
       Me.txtTime.Value = Format(dblNum, "00:00")
    End If
      
End Sub

Private Sub cboZulu_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If IsNumeric(Me.cboZulu.Value) Then
        dblNum = Me.cboZulu.Value
        Me.cboZulu.Value = Format(dblNum, "00:00")
    End If
    
    If IsNumeric(Me.txtZulu.Value) Then
        dblNum = Me.txtZulu.Value
        Me.txtZulu.Value = Format(dblNum, "00:00")
    End If
    
    Me.txtZulu.Value = Val(Me.txtTime.Value) + Val(Me.cboZulu.Value)
    
End Sub

Private Sub cboZulu_updateform()
    
End Sub
 
Last edited by a moderator:

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
If you put either debug.print(iRow), or msgbox(iRow), before the "with ws" block, what do you get?

Form looking at the first few lines I'd hazard a guess that it can't find a row and the msgbox will give you an error. But that's just a first debug.
 
Upvote 0
What error message did you get? & what was the value of iRow?

Also when posting code please use code tags, the # icon in the reply window.
 
Upvote 0
still get same debugger window with debug.print(iRow), and get msgbox window with 1048577 then to debugger window and still pointing to .Cells(iRow, 1).Value = Me.txtDate.Value
 
Upvote 0
and get msgbox window with 1048577
That's why you have a problem.
Your code is trying to add the data to a row that doesn't exist.
Somewhere in your sheet you have a value in row 1048576, which is the very last row.
 
Upvote 0
Solution
Found it, not sure how formula's got entered into those cells, but that did the trip, thank you again Fluff, you've helped me more than once. Thank you NeonRedSharpie, I'll try and remember that msgbox trick. I'm still trying to learn this VBA code.
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top