| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format a rangeafter usining a userform
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Format a rangeafter usining a userform
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" wrote in message .. . 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Format a rangeafter usining a userform
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" wrote in message ... 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" wrote in message .. . 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Format a rangeafter usining a userform
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" wrote in message .. . 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" wrote in message ... 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" wrote in message .. . 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Format a rangeafter usining a userform
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 "Bob Phillips" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Is there a way to have fixed format in a userform? | Excel Discussion (Misc queries) | |||
| format userform text box | Excel Discussion (Misc queries) | |||
| Date format in userform | Excel Programming | |||
| How to format Userform caption? | Excel Programming | |||
| Userform format question | Excel Programming | |||
Hybrid Mode
