VBA/Macros to Control Active Cell

Hello To Everyone Taking A Look At This,

First off, I must say I am very thankful for this forum and John Topley for the Wile E. Coyote, Super Genius help he was able to provide. I hope I can find another Super Genius to help me with VBA/Macros code. And I must let you know I know nothing about it. I have successfully screwed up 4 attempts of creating code, once while watching a YouTube video. Doom on me!

Overview, a user enters data into the RO-CT and Daily Database sheets (I have populated June 1-4 to provide a working sample). Then on the Daily Report sheet the user performs one action, enters a date in cell B2 and the entire Daily Report becomes populated by date, pulling and calculating the information from both database sheets.

The issue at hand is the way in which the active cell moves down the page when hitting the enter key and the obnoxious scroll to get to the 1500th row to enter the next bit of data as this database grows.

On the RO-CT Database Sheet:
1 Upon opening the active cell will always be A2
2 Upon hitting the Enter key the active cell will move to column B, then C, then D, and so on until Q (Tax) when the entry is considered completed (Ignore column R (Total) , it is just a SUM column for error checking).
3 When cell in Column Q (Tax) is active hitting the Enter key will 1. Push data entered in row 2 down to row 3, and 2. Reset the active cell back to A2, ready to enter the date from the next invoice.
Lastly, can RO/CT Number Column be coded to always capitalize (defiantly) even if it needed to be moved in the future (icing on the cake)?

On the Daily Database Sheet:
1 Upon opening the active cell will always be A2
When working in the Customer Collection Section
2 Upon hitting the Enter key the active cell will move to column B, then C.
3 When cell in Column C (Amount) is active hitting the Enter key will 1. Push data entered in row 2 down to row 3, and 2. Reset the active cell back to A2, ready to enter more data.
When working in the Cash Paid Out Section
4 When cell E2 becomes active hitting the enter key the active cell will move to column F, then G.
5 When cell in Column G (Amount) is active hitting the Enter key will 1. Push data entered in row 2 down to row 3, and 2. Reset the active cell back to E2, ready to enter more data.
When working in the Final Section
6 When cell I2 becomes active hitting the enter key the active cell will move to column J, then K, and so on to column N.
7 When cell in Column N (Bank Deposit) is active hitting the Enter key will 1. Push data entered in row 2 down to row 3, and 2. Reset the active cell back to I2, ready to enter more data.

The overall idea is to have each section act or feel as if it is its own little mini database even though it is contained on a single sheet.

I know for the most part most of this is a repeat of the same code with different cell references and for someone in the know this is probably a joke; however, I appreciate you for taking a look at it.

Thank you.

Hi MarvinP,

First off, Go Hawks! Throw back, favorite AFC West Team! (No one can hold it against it me since my team is the Jets)

Down to business.

I appreciate your insight, just not looking for a form. Already loaded it up and it was big and long, whereas as columns of Excel of nicely on my screen and as you can see most of the work is 10key. I have seen the code for what I want to accomplish on the internet, but when I attempt to copy and paste to create a macros, I Chernobyl my workbook. As I stated, I know what I would like my end result to be, but have no clue as to how get there other than to ask for help.

Updated version: now adds records to the end.

Private Sub CommandButton2_Click()

Worksheets("RO-CT Database").Activate

lr = Cells(Rows.Count, "A").End(xlUp).Row + 1

For i = 1 To 17
Cells(lr, i) = Me.Controls("TextBox" & i)
If i = 3 Then Cells(lr, 3) = UCase(Me.Controls("TextBox" & i))
If i >= 4 Then Cells(lr, i) = Val(Cells(lr, i))
Next

End Sub



Private Sub CommandButton4_Click()

Worksheets("Daily Database").Activate

lr = Cells(Rows.Count, "A").End(xlUp).Row + 1

For i = 1 To 3
Cells(lr, i) = Me.Controls("TextBox" & 32 + i)
If i = 1 Then Cells(lr, i) = DateValue(Cells(lr, i))
If i = 3 Then Cells(lr, i) = Val(Cells(lr, i))
Next

For i = 1 To 3

Cells(lr, i + 4) = Me.Controls("TextBox" & 35 + i)
If i = 1 Then Cells(lr, i + 4) = DateValue(Cells(lr, i + 4))
If i = 3 Then Cells(lr, i + 4) = Val(Cells(lr, i + 4))
Next i

For i = 1 To 6
Cells(lr, i + 8) = Me.Controls("TextBox" & 38 + i)
If i = 1 Then Cells(lr, i + 8) = DateValue(Cells(lr, i + 4))
If i >= 2 Then Cells(lr, i + 8) = Val(Cells(lr, i + 8))
Next

End Sub

See attached.

Further edits added:

rivate Sub CommandButton2_Click()

Dim Chksum1 As Double
Dim Chksum2 As Double
'
'  Check if validate dates
'

If Not (IsDate(Me.Controls("TextBox1"))) Or Not (IsDate(Me.Controls("TextBox2"))) Then
    Msg = MsgBox("Input error in Date fields" & vbCrLf & vbCrLf & "Please correct", vbExclamation, "Date errors")
    Exit Sub
End If

FindString = UCase(Me.Controls("TextBox3"))
'
' Check for duplicate RO/CT number
'
If Trim(FindString) <> "" Then
        With Sheets("RO-CT Database").Range("C:C")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                  Msg = MsgBox("Duplicate RO/CT number: " & FindString & vbCrLf & vbCrLf & "Do you want to continue ?" _
                , vbYesNo + vbExclamation, "RO/CT  number")

                If Msg = vbNo Then
                  MsgBox "Please correct the data"
                  Exit Sub
                Else
                 GoTo nextchk
                End If
'
'  Check if RO/CT number valid format
'
            Else
                If Left(FindString, 2) <> "RO" And Left(FindString, 2) <> "CT" Then
                    MsgBox "Invalid RO/CT number: " & FindString & vbCrLf & vbCrLf & "Please correct data"
                    Exit Sub
                End If
            End If
        End With
    End If

nextchk:
'
'  Check Financial balance
'

Chksum1 = 0
For i = 4 To 7
Chksum1 = Chksum1 + Val(Me.Controls("textbox" & i))
Next i
Chksum2 = 0
For i = 8 To 17
Chksum2 = Chksum2 + Val(Me.Controls("textbox" & i))
Next i
If Chksum1 - Chksum2 <> 0 Then

   Msg = MsgBox("Finance checksum does not balance: " & vbCrLf & vbCrLf & "Do you want to continue ?" _
                , vbYesNo + vbExclamation, "Finance Checksum")

            If Msg = vbNo Then
              MsgBox "Please correct the data"
              Exit Sub
            End If


End If

Worksheets("RO-CT Database").Activate

lr = Cells(Rows.Count, "A").End(xlUp).Row + 1

For i = 1 To 17
Cells(lr, i) = Me.Controls("TextBox" & i)
If i <= 2 Then Cells(lr, i) = Format(DateValue(Cells(lr, i)), "mm/dd/yy")
If i = 3 Then Cells(lr, 3) = UCase(Me.Controls("TextBox" & i))
If i >= 4 Then Cells(lr, i) = Val(Cells(lr, i))
Next



End Sub