VBA Code that is kicking my behind...any help would be appreciated!

T

TheRandy

New Member
Joined
Jul 10, 2015
Messages
5
Good morning. I've been tinkering with this code for sometime now and I cant seem to get it to work. When the user clicks on the NextCom command, it does add the entries to the worksheet and it does clear the form, but when the next record is submitted, it just overwrites that last one...an ongoing loop overwriting the same entry. Any help PLEASE!!! I'm a rookie when it comes to VBA so please be gentle if its jacked! Thanks in advance

NOTE: Also, I have some cells locked on the spreadsheet to negate any altering to the information...but the cells where the information is going are all unlocked

Code: 
Private Sub CloseCom_Click()
    Unload Me
End Sub


Private Sub NextCom_Click()
    Dim RowCount As Long
    Dim ctl As Control




    If Me.txtCR.Value = "" Then
        MsgBox "Please enter the current reading", vbExclamation, "Invalid Reading"
        Me.txtCR.SetFocus
        Exit Sub
    End If
    If Me.cboMTUMatch.Value = "" Then
        MsgBox "Does the MTU Match?", vbExclamation, "Answer Required"
        Me.cboMTUMatch.SetFocus
        Exit Sub
    End If
    If Me.cboMeterMatch.Value = "" Then
        MsgBox "Does the Meter SN Match?", vbExclamation, "Answer Required"
        Me.cboMeterMatch.SetFocus
        Exit Sub
    End If
    If Me.cboBox.Value = "" Then
        MsgBox "Please select Box Type", vbExclamation, "Answer Required"
        Me.cboBox.SetFocus
        Exit Sub
    End If
    If Me.cboLid.Value = "" Then
        MsgBox "Please select Lid Type", vbExclamation, "Answer Required"
        Me.cboLid.SetFocus
        Exit Sub
    End If
    If Me.cboMTUType.Value = "" Then
        MsgBox "Please select MTU Type", vbExclamation, "Answer Required"
        Me.cboMTUType.SetFocus
        Exit Sub
    End If
    If Me.cboMTULo.Value = "" Then
        MsgBox "Please select MTU Location", vbExclamation, "Answer Required"
        Me.cboMTULo.SetFocus
        Exit Sub
    End If
    If Me.cboPitFl.Value = "" Then
        MsgBox "Is the pit flooded?", vbExclamation, "Answer Required"
        Me.cboPitFl.SetFocus
        Exit Sub
    End If
    


emptyRow = ActiveSheet.Cells(Rows.Count, 8).End(xlUp).Row + 1


    With Worksheets("Sheet1").Range("I3")
        .Offset(RowCount, 0).Value = Me.txtCR.Value
        .Offset(RowCount, 1).Value = Me.cboMTUMatch.Value
        .Offset(RowCount, 2).Value = Me.txtFixMTU.Value
        .Offset(RowCount, 3).Value = Me.cboMeterMatch.Value
        .Offset(RowCount, 4).Value = Me.txtFixMeter.Value
        .Offset(RowCount, 5).Value = Me.cboBox.Value
        .Offset(RowCount, 6).Value = Me.cboLid.Value
        .Offset(RowCount, 7).Value = Me.cboMTUType.Value
        .Offset(RowCount, 8).Value = Me.cboMTULo.Value
        .Offset(RowCount, 9).Value = Me.cboPitFl.Value
    End With
    


    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
            ctl.Value = ""
        ElseIf TypeName(ctl) = "CheckBox" Then
            ctl.Value = False
        End If
    Next ctl
End Sub
 
Last edited:

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.
Sort by date Sort by votes
Nothing is giving a value to RowCount as far as I can see?
I presume it's supposed to be using the value attributed to emptyRow as that doesn't get referenced anywhere other than where you define it's value.
 
Upvote 0
So what is your suggestion? Am I missing a line of code? Does something need to be added to my emptyRow line? Sorry, as I stated in my original post, I'm a rookie
 
Upvote 0
TheRandy,

Code: 
emptyRow = ActiveSheet.Cells(Rows.Count, 8).End(xlUp).Row + 1


    With Worksheets("Sheet1").Range("I3")
        .Offset(RowCount, 0).Value = Me.txtCR.Value
        .Offset(RowCount, 1).Value = Me.cboMTUMatch.Value
        .Offset(RowCount, 2).Value = Me.txtFixMTU.Value
        .Offset(RowCount, 3).Value = Me.cboMeterMatch.Value
        .Offset(RowCount, 4).Value = Me.txtFixMeter.Value
        .Offset(RowCount, 5).Value = Me.cboBox.Value
        .Offset(RowCount, 6).Value = Me.cboLid.Value
        .Offset(RowCount, 7).Value = Me.cboMTUType.Value
        .Offset(RowCount, 8).Value = Me.cboMTULo.Value
        .Offset(RowCount, 9).Value = Me.cboPitFl.Value
    End With

1. What worksheet does the next line of code refer to:

Code: 
emptyRow = ActiveSheet.Cells(Rows.Count, 8).End(xlUp).Row + 1

2. Are you attempting to write to the next blank row in Worksheets("Sheet1").Range("I emptyRow")
 
Last edited:
Upvote 0
I'd be inclined to give this a shot..

Code: 
emptyRow = ActiveSheet.Cells(Rows.Count, 8).End(xlUp).Row + 1




    With Worksheets("Sheet1").Range("I3")
        .Offset(emptyRow , 0).Value = Me.txtCR.Value
        .Offset(emptyRow , 1).Value = Me.cboMTUMatch.Value
        .Offset(emptyRow , 2).Value = Me.txtFixMTU.Value
        .Offset(emptyRow , 3).Value = Me.cboMeterMatch.Value
        .Offset(emptyRow , 4).Value = Me.txtFixMeter.Value
        .Offset(emptyRow , 5).Value = Me.cboBox.Value
        .Offset(emptyRow , 6).Value = Me.cboLid.Value
        .Offset(emptyRow , 7).Value = Me.cboMTUType.Value
        .Offset(emptyRow , 8).Value = Me.cboMTULo.Value
        .Offset(emptyRow , 9).Value = Me.cboPitFl.Value
    End With

Note : It's untested.
 
Upvote 0
TheRandy,

If you are attempting to write to Worksheets("Sheet1").Range("I" the next empty row, then give the following macro change a try on a copy of your actual workbook.


Code: 
    emptyRow = Worksheets("Sheet1").Cells(Rows.Count, "I").End(xlUp).Row + 1
    
    With Worksheets("Sheet1").Range("I" & emptyRow)
        .Offset(, 0).Value = Me.txtCR.Value
        .Offset(, 1).Value = Me.cboMTUMatch.Value
        .Offset(, 2).Value = Me.txtFixMTU.Value
        .Offset(, 3).Value = Me.cboMeterMatch.Value
        .Offset(, 4).Value = Me.txtFixMeter.Value
        .Offset(, 5).Value = Me.cboBox.Value
        .Offset(, 6).Value = Me.cboLid.Value
        .Offset(, 7).Value = Me.cboMTUType.Value
        .Offset(, 8).Value = Me.cboMTULo.Value
        .Offset(, 9).Value = Me.cboPitFl.Value
    End With
 
Last edited:
Upvote 0
hiker95 said:
TheRandy,

If you are attempting to write to Worksheets("Sheet1").Range("I" the next empty row, then give the following macro change a try on a copy of your actual workbook.


Code: 
    emptyRow = Worksheets("Sheet1").Cells(Rows.Count, "I").End(xlUp).Row + 1
    
    With Worksheets("Sheet1").Range("I" & emptyRow)
        .Offset(, 0).Value = Me.txtCR.Value
        .Offset(, 1).Value = Me.cboMTUMatch.Value
        .Offset(, 2).Value = Me.txtFixMTU.Value
        .Offset(, 3).Value = Me.cboMeterMatch.Value
        .Offset(, 4).Value = Me.txtFixMeter.Value
        .Offset(, 5).Value = Me.cboBox.Value
        .Offset(, 6).Value = Me.cboLid.Value
        .Offset(, 7).Value = Me.cboMTUType.Value
        .Offset(, 8).Value = Me.cboMTULo.Value
        .Offset(, 9).Value = Me.cboPitFl.Value
    End With
Click to expand...

This worked...Can you explain to me what made the change so I can learn from this? Thanks for your help!

Thank you all for your quick responses!! New member and will definitely be coming back!
 
Upvote 0
TheRandy,

This worked...
Thank you all for your quick responses!! New member and will definitely be coming back!
Click to expand...

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

Can you explain to me what made the change so I can learn from this?
Click to expand...

Sure - be back in a little while.
 
Upvote 0
TheRandy,

Can you explain to me what made the change so I can learn from this?
Click to expand...

Please see the comment lines in the below macro code, that begin with the ' characters.

I hope the comments will help you understand.

Rich (BB code): 
    ' It is a good programming practice to declare all your variables.
    ' If there was a problem with your macro code, then,
    '   someone with programming experience can usually find, and, fix the problem.
    Dim emptyRow As Long

    ' You are writing to Worksheets("Sheet1")
    ' And, you want to find the next available/blank row
    '   in column I of Worksheets("Sheet1")
    emptyRow = Worksheets("Sheet1").Cells(Rows.Count, "I").End(xlUp).Row + 1


    ' If the next empty row was row 25
    '   then the next line of code
    With Worksheets("Sheet1").Range("I" & emptyRow)

    ' would translate to
    With Worksheets("Sheet1").Range("I25")


        ' then the next line of code
        .Offset(, 0).Value = Me.txtCR.Value

        ' would translate to
        .Range("I25").Value = Me.txtCR.Value


        ' then the next line of code
        .Offset(, 1).Value = Me.cboMTUMatch.Value

        ' would translate to
        .Range("J25").Value = Me.cboMTUMatch.Value


        ' the following lines of code would be similar
        .Offset(, 2).Value = Me.txtFixMTU.Value
        .Offset(, 3).Value = Me.cboMeterMatch.Value
        .Offset(, 4).Value = Me.txtFixMeter.Value
        .Offset(, 5).Value = Me.cboBox.Value
        .Offset(, 6).Value = Me.cboLid.Value
        .Offset(, 7).Value = Me.cboMTUType.Value
        .Offset(, 8).Value = Me.cboMTULo.Value
        .Offset(, 9).Value = Me.cboPitFl.Value
    End With
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Sense checking UserFrom code
Replies
0
Views
289
AndyEd
A
S
  • Solved
VBA: Am I double-calling a Sub given nested If statements? Or other inefficiencies?
Replies
8
Views
260
dreid1011
dreid1011
B
  • Question
VBA code to cut and paste multiple filtered rows based on multiple criteria
Replies
2
Views
569
Alex Blakenburg
A
S
  • Question
Need code that adds multiple new rows to table to include a check box in column "L" for each new row
Replies
0
Views
353
sspatriots
S
E
  • Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
301
offthelip
O

Forum statistics

Threads
1,215,454
Messages
6,124,933
Members
449,195
Latest member
Stevenciu

Share this page

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