Format a rangeafter usining a userform

[DennisB]

I posted this a few days ago and got one reply from Bob Phillips which
unfortunately didn't work.

I have written a Macro to record Registration information for up to 2,000
people registered at a Lions Convention.

I use a userform to enter the information for each Registration. Based on
the type of Registration, I would like to be able to highlight the LastRow
in an appropriate color. Since the database is dynamic, I am having
difficulty selecting the Last row (Uses A(lastRow):l(Lastrow)). This is the
code I have written (albeit not well) so far. It will highlight the very
first cell in the LastRow but will not select entire row.

If anyone can steer me straight I would appreciate it.

My Code
=================================
Private Sub CommandButton1_Click()


Dim LastRow As Object
Dim Response As String
Dim Ts As Integer, Lu As Integer, Sheets2 As Worksheet

' Application.Visible = False
Set LastRow = Sheet1.Range("a65536").End(xlUp)

Ts = 0
Lu = 0
If Sheets.Count <> 1 Then Sheets(1).Activate
LastRow.Activate

LastRow.Offset(1, 0).Value = txtName.Text
LastRow.Offset(1, 1).Value = txtClub.Text
LastRow.Offset(1, 2).Value = cmbDist.Text
LastRow.Offset(1, 3).Value = 1 ' txtPersons.Text

On Error Resume Next

If CheckBox1 = True Or CheckBox2 = True Then
LastRow.Offset(1, 4).Value = 10
Else
LastRow.Offset(1, 4).Value = 20
End If

If CheckBox1 = True Then
MsgBox ("Sun Only - No Room deposit required!")
LastRow.Offset(1, 5).Value = ""
End If

If CheckBox2 = True Then
MsgBox ("This is a Leo - Registration is only One Half!")

Else
LastRow.Offset(1, 5).Value = txtRoomDep.Text
End If

LastRow.Offset(1, 8).Value = txtLunch.Text
Lu = LastRow.Offset(1, 8).Value * 25
Ts = Ts + Lu
LastRow.Offset(1, 9).Value = txtTheatre.Text
Lu = LastRow.Offset(1, 9).Value * 30
Ts = Ts + Lu
LastRow.Offset(1, 10).Value = txtBanquet.Text
Lu = LastRow.Offset(1, 10).Value * 55
Ts = Ts + Lu
LastRow.Offset(1, 11).Value = txtDance.Text
Lu = LastRow.Offset(1, 11).Value * 5
Ts = Ts + Lu
LastRow.Offset(1, 6).Value = Ts

If OptionButton1 = True Then
LastRow.Offset(1, 14).Value = "M" ' Using Master Card
End If
If OptionButton2 = True Then
LastRow.Offset(1, 14).Value = "V" ' Using Visa
End If
Set LastRow = Sheet1.Range("a65536").End(xlUp)
LastRow.Activate

Range.Cells("A(LastRow):L(LastRow)").Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.ColorIndex = 10
End With


MsgBox "One record written to Sheet1"


=====================================================

DennisB

 

[Bob Phillips]

It would be better to stick to the same thread, so everyone can see what has
already been offered.

What didn't work with that suggestion.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[DennisB]

Bob;

You suggested using Range.Cells("A" & LastRow & ":L" & LastRow).Select.

When I tried this it highlighted an error on the second instance of Lastrow.

As I said it does select the first cell in the range but does not select the
complete range

DennisB

 

[Bob Phillips]

Dennis,

I see what the problem is, you have declared LastRow as an object, I thought
it was long.

Try this

Private Sub CommandButton1_Click()
Dim LastRow As Range
Dim Response As String
Dim Ts As Integer, Lu As Integer, Sheets2 As Worksheet

' Application.Visible = False
Set LastRow = Sheet1.Range("A" & Rows.Count).End(xlUp)


Ts = 0
Lu = 0
If Sheets.Count <> 1 Then Sheets(1).Activate
LastRow.Activate

LastRow.Offset(1, 0).Value = txtName.Text
LastRow.Offset(1, 1).Value = txtClub.Text
LastRow.Offset(1, 2).Value = cmbDist.Text
LastRow.Offset(1, 3).Value = 1 ' txtPersons.Text

On Error Resume Next

If CheckBox1 = True Or CheckBox2 = True Then
LastRow.Offset(1, 4).Value = 10
Else
LastRow.Offset(1, 4).Value = 20
End If

If CheckBox1 = True Then
MsgBox ("Sun Only - No Room deposit required!")
LastRow.Offset(1, 5).Value = ""
End If

If CheckBox2 = True Then
MsgBox ("This is a Leo - Registration is only One Half!")

Else
LastRow.Offset(1, 5).Value = txtRoomDep.Text
End If

LastRow.Offset(1, 8).Value = txtLunch.Text
Lu = LastRow.Offset(1, 8).Value * 25
Ts = Ts + Lu
LastRow.Offset(1, 9).Value = txtTheatre.Text
Lu = LastRow.Offset(1, 9).Value * 30
Ts = Ts + Lu
LastRow.Offset(1, 10).Value = txtBanquet.Text
Lu = LastRow.Offset(1, 10).Value * 55
Ts = Ts + Lu
LastRow.Offset(1, 11).Value = txtDance.Text
Lu = LastRow.Offset(1, 11).Value * 5
Ts = Ts + Lu
LastRow.Offset(1, 6).Value = Ts

If OptionButton1 = True Then
LastRow.Offset(1, 14).Value = "M" ' Using Master Card
End If
If OptionButton2 = True Then
LastRow.Offset(1, 14).Value = "V" ' Using Visa
End If
Set LastRow = Sheet1.Range("a65536").End(xlUp)
LastRow.Activate

Cells("A" & LastRow.Row & ":L" & LastRow.Row).Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.ColorIndex = 10
End With


MsgBox "One record written to Sheet1"

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[DennisB]

Bob;
I put your suggestion into my program and it now works perfectly. Your help
was very much appreciated.

I have fought with this for quite a while with no luck. Inever thought of
declaring the "LastRow" as a Range and using "Rows.Count" in the Set
statement. I am fairly new at VBA programing but I am learning a tremendous
amount with the help of this NG and people like yourself.

Thanks again.

DennisB

Dennis,

I see what the problem is, you have declared LastRow as an object, I
thought
it was long.

Try this

Private Sub CommandButton1_Click()
Dim LastRow As Range
Dim Response As String
Dim Ts As Integer, Lu As Integer, Sheets2 As Worksheet

' Application.Visible = False
Set LastRow = Sheet1.Range("A" & Rows.Count).End(xlUp)

<<rest of Code snipped>>