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

dlmoore99

Board Regular
Joined
May 20, 2011
Messages
55
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:

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,628
Office Version
365
Platform
Windows
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.
 

dlmoore99

Board Regular
Joined
May 20, 2011
Messages
55
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,628
Office Version
365
Platform
Windows
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.
 

dlmoore99

Board Regular
Joined
May 20, 2011
Messages
55
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,628
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Forum statistics

Threads
1,089,489
Messages
5,408,565
Members
403,215
Latest member
DblDocWhitaker

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top