Help with shortening my routine

[Skip Bisconer]

I have created a backup log form to post backup history and I have this tremendous amount of typing involve in my VBA. Can some one take a look for me and tell me how it can be reduced and still do the job?

Thanks for looking

Skip

Private Sub cmdPostData_Click()
Dim NextRow

Worksheets("Skip").Activate


NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1
'Find the next available Row to post data

Worksheets("Skip").Activate

Cells(NextRow, 1) = txtDate.Value

If sxb1 = True Then Cells(NextRow, 2).Value = "Yes"

If sxb1 = False Then
Cells(NextRow, 2).Interior.ColorIndex = 6
Cells(NextRow, 2).Value = "No"
End If

If sxb2 = True Then Cells(NextRow, 3).Value = "Yes "

If sxb2 = False Then
Cells(NextRow, 3).Interior.ColorIndex = 6
Cells(NextRow, 3).Value = "No"
End If

If sxb3 = True Then Cells(NextRow, 4).Value = "Yes"

If sxb3 = False Then
Cells(NextRow, 4).Interior.ColorIndex = 6
Cells(NextRow, 4).Value = "No"
End If

If sxb4 = True Then Cells(NextRow, 5).Value = "Yes"

If sxb4 = False Then
Cells(NextRow, 5).Interior.ColorIndex = 6
Cells(NextRow, 5).Value = "No"
End If

If mxb1 = True Then
Cells(NextRow, 6).Value = "CD"
Cells(NextRow, 6).HorizontalAlignment = xlCenter
End If

If mxb2 = True Then
Cells(NextRow, 6).Value = "DVD"
Cells(NextRow, 6).HorizontalAlignment = xlCenter
End If

If mxb3 = True Then
Cells(NextRow, 6).Value = "Skips Computer"
Cells(NextRow, 6).HorizontalAlignment = xlCenter
End If

Cells(NextRow, 1).Select

Worksheets("Rosalie").Activate

NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1

Cells(NextRow, 1).Value = txtDate
If rxb1 = True Then Cells(NextRow, 2).Value = "Yes"

If rxb1 = False Then
Cells(NextRow, 2).Interior.ColorIndex = 6
Cells(NextRow, 2).Value = "No"
End If

If rxb2 = True Then Cells(NextRow, 3).Value = "Yes "

If rxb2 = False Then
Cells(NextRow, 3).Interior.ColorIndex = 6
Cells(NextRow, 3).Value = "No"
End If

If rxb3 = True Then Cells(NextRow, 4).Value = "Yes"

If sxb3 = False Then
Cells(NextRow, 4).Interior.ColorIndex = 6
Cells(NextRow, 4).Value = "No"
End If

If mxb1 = True Then
Cells(NextRow, 5).Value = "CD"
Cells(NextRow, 5).HorizontalAlignment = xlCenter
End If

If mxb2 = True Then
Cells(NextRow, 5).Value = "DVD"
Cells(NextRow, 5).HorizontalAlignment = xlCenter
End If


If mxb3 = True Then
Cells(NextRow, 5).Value = "Skips Computer"
Cells(NextRow, 5).HorizontalAlignment = xlCenter
End If

Cells(NextRow, 1).Select


sxb1 = False
sxb2 = False
sxb3 = False
sxb4 = False
rxb1 = False
rxb2 = False
rxb3 = False
mbx1 = False
mbx2 = False
mbx3 = False

Unload BackupForm

End Sub

 

[Norie]

Skip

What are sxb1, sxb2 etc?

 

[Skip Bisconer]

Sorry I didn't say. But the sxb1 etc are check boxes


Skip

 

[Norie]

Skip

Why not use option buttons for mxb1-3?

Also why not use If Else End If?

Private Sub cmdPostData_Click()
Dim NextRow

Worksheets("Skip").Activate

NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1

ws.Cells(NextRow, 1) = txtDate.Value

If sxb1 = True Then
    Cells(NextRow, 2).Value = "Yes"
Else
    Cells(NextRow, 2).Interior.ColorIndex = 6
    Cells(NextRow, 2).Value = "No"
End If

If sxb2 = True Then
    Cells(NextRow, 3).Value = "Yes "
Else
    Cells(NextRow, 3).Interior.ColorIndex = 6
    Cells(NextRow, 3).Value = "No"
End If

If sxb3 = True Then
    Cells(NextRow, 4).Value = "Yes"
Else
    Cells(NextRow, 4).Interior.ColorIndex = 6
    Cells(NextRow, 4).Value = "No"
End If

If sxb4 = True Then
    Cells(NextRow, 5).Value = "Yes"
Else
    Cells(NextRow, 5).Interior.ColorIndex = 6
    Cells(NextRow, 5).Value = "No"
End If

If mxb1 = True Then
    Cells(NextRow, 6).Value = "CD"
    Cells(NextRow, 6).HorizontalAlignment = xlCenter
End If

If mxb2 = True Then
    Cells(NextRow, 6).Value = "DVD"
    Cells(NextRow, 6).HorizontalAlignment = xlCenter
End If

If mxb3 = True Then
    Cells(NextRow, 6).Value = "Skips Computer"
    Cells(NextRow, 6).HorizontalAlignment = xlCenter
End If

Worksheets("Rosalie").Activate

NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1

Cells(NextRow, 1).Value = txtDate

If rxb1 = True Then
    Cells(NextRow, 2).Value = "Yes"
Else
    Cells(NextRow, 2).Interior.ColorIndex = 6
    Cells(NextRow, 2).Value = "No"
End If

If rxb2 = True Then
    Cells(NextRow, 3).Value = "Yes "
Else
    Cells(NextRow, 3).Interior.ColorIndex = 6
    Cells(NextRow, 3).Value = "No"
End If

If rxb3 = True Then
    Cells(NextRow, 4).Value = "Yes"
Else
    Cells(NextRow, 4).Interior.ColorIndex = 6
    Cells(NextRow, 4).Value = "No"
End If

If mxb1 = True Then
    Cells(NextRow, 5).Value = "CD"
    Cells(NextRow, 5).HorizontalAlignment = xlCenter
End If

If mxb2 = True Then
    Cells(NextRow, 5).Value = "DVD"
    Cells(NextRow, 5).HorizontalAlignment = xlCenter
End If

If mxb3 = True Then
    Cells(NextRow, 5).Value = "Skips Computer"
    Cells(NextRow, 5).HorizontalAlignment = xlCenter
End If

Cells(NextRow, 1).Select

sxb1 = False
sxb2 = False
sxb3 = False
sxb4 = False
rxb1 = False
rxb2 = False
rxb3 = False
mbx1 = False
mbx2 = False
mbx3 = False

Unload BackupForm

End Sub

 

[Skip Bisconer]

Thanks, That makes it look a lot better.

Skip