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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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

P
  • Question Question
How to VBA code to drop data from UserForm to multiple worksheets
2
Replies
17
Views
594
dmt32
D
B
  • Question Question
VBA Code to Send Mail to skip blank cells
Replies
1
Views
269
Micron
M
N
  • Question Question
Excel VBA - issue with my VBA code
Replies
3
Views
334
dmt32
D
A
  • Question Question
Sense checking UserFrom code
Replies
0
Views
424
AndyEd
A
M
  • Solved
Help to exit VBA upon cancel on input box
Replies
3
Views
490
Domenic
D

Forum statistics

Threads
1,223,228
Messages
6,170,875
Members
452,363
Latest member
merico17

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