Case Statement Help
 

I am new to vba so please bear with me. I have a small userform that ha
5 option buttons in a frame. I have a spread sheet that has 5 columns
If the user selects the first button, I want the value to go into th
first coulmn. The second button, if selected has its value go into th
second column and so on. I have enclosed the code below. I am reall
stumped here.


Private Sub cmdOK_Click()
ActiveWorkbook.Sheets("Active Collection").Activate
Range("A1").Select

Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Value = "Test"
ActiveCell.Offset(0, 1) = Date
ActiveCell.Offset(0, 2) = Time
ActiveCell.Offset(0, 3) = txtCompany.Value
ActiveCell.Offset(0, 4) = txtName.Value
ActiveCell.Offset(0, 5) = txtPhone.Value
ActiveCell.Offset(0, 6) = txtInvoiceNo.Value
ActiveCell.Offset(0, 7) = cmbInvoiceType.Value
ActiveCell.Offset(0, 8) = txtInvoiceDate.Value
ActiveCell.Offset(0, 9) = txtAmount.Value
ActiveCell.Offset(0, 10) = txtSubStartDate.Value
ActiveCell.Offset(0, 11) = txtWhichInvoice.Value
ActiveCell.Offset(0, 12) = txtPaid.Value

Select Case Late
Case opt30.Value Is Checked
ActiveCell.Offset(0, 13) = txtPaid.Value
Case opt60.Value Is Checked
ActiveCell.Offset(0, 14) = txtPaid.Value
Case opt90.Value Is Checked
ActiveCell.Offset(0, 15) = txtPaid.Value
Case opt120.Value Is Checked
ActiveCell.Offset(0, 16) = txtPaid.Value
Case opt121.Value Is Checked
ActiveCell.Offset(0, 17) = txtPaid.Value
End Select

ActiveCell.Offset(0, 18) = "Invoice Amount"
ActiveCell.Offset(0, 19) = "Amount 1"
ActiveCell.Offset(0, 20) = "Amount 1"
ActiveCell.Offset(0, 21) = txtComments.Value

Range("A1").Select
Call frmNewCollect_Initialize
End Su

--
Message posted from http://www.ExcelForum.com

Case Statement Help
 

Select Case True
Case opt30.Value
ActiveCell.Offset(0, 13) = txtPaid.Value
Case opt60.Value
ActiveCell.Offset(0, 14) = txtPaid.Value
Case opt90.Value
ActiveCell.Offset(0, 15) = txtPaid.Value
Case opt120.Value
ActiveCell.Offset(0, 16) = txtPaid.Value
Case opt121.Value
ActiveCell.Offset(0, 17) = txtPaid.Value
End Select

--
Regards,
Tom Ogilvy

"stck2mlon " wrote in message
...
I am new to vba so please bear with me. I have a small userform that has
5 option buttons in a frame. I have a spread sheet that has 5 columns.
If the user selects the first button, I want the value to go into the
first coulmn. The second button, if selected has its value go into the
second column and so on. I have enclosed the code below. I am really
stumped here.


Private Sub cmdOK_Click()
ActiveWorkbook.Sheets("Active Collection").Activate
Range("A1").Select

Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Value = "Test"
ActiveCell.Offset(0, 1) = Date
ActiveCell.Offset(0, 2) = Time
ActiveCell.Offset(0, 3) = txtCompany.Value
ActiveCell.Offset(0, 4) = txtName.Value
ActiveCell.Offset(0, 5) = txtPhone.Value
ActiveCell.Offset(0, 6) = txtInvoiceNo.Value
ActiveCell.Offset(0, 7) = cmbInvoiceType.Value
ActiveCell.Offset(0, 8) = txtInvoiceDate.Value
ActiveCell.Offset(0, 9) = txtAmount.Value
ActiveCell.Offset(0, 10) = txtSubStartDate.Value
ActiveCell.Offset(0, 11) = txtWhichInvoice.Value
ActiveCell.Offset(0, 12) = txtPaid.Value

Select Case Late
Case opt30.Value Is Checked
ActiveCell.Offset(0, 13) = txtPaid.Value
Case opt60.Value Is Checked
ActiveCell.Offset(0, 14) = txtPaid.Value
Case opt90.Value Is Checked
ActiveCell.Offset(0, 15) = txtPaid.Value
Case opt120.Value Is Checked
ActiveCell.Offset(0, 16) = txtPaid.Value
Case opt121.Value Is Checked
ActiveCell.Offset(0, 17) = txtPaid.Value
End Select

ActiveCell.Offset(0, 18) = "Invoice Amount"
ActiveCell.Offset(0, 19) = "Amount 1"
ActiveCell.Offset(0, 20) = "Amount 1"
ActiveCell.Offset(0, 21) = txtComments.Value

Range("A1").Select
Call frmNewCollect_Initialize
End Sub


---
Message posted from http://www.ExcelForum.com/

Case Statement Help
 

Select Case True
Case opt30.Value : idx = 1
Case opt60.Value : idx = 2
Case opt90.Value : idx = 3
Case opt20.Value : idx = 4
Case opt21.Value : idx = 5
end select

ActiveCell.Offset(0, i+12) = txtPaid.Value


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"stck2mlon " wrote in message
...
I am new to vba so please bear with me. I have a small userform that has
5 option buttons in a frame. I have a spread sheet that has 5 columns.
If the user selects the first button, I want the value to go into the
first coulmn. The second button, if selected has its value go into the
second column and so on. I have enclosed the code below. I am really
stumped here.


Private Sub cmdOK_Click()
ActiveWorkbook.Sheets("Active Collection").Activate
Range("A1").Select

Do

If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Value = "Test"
ActiveCell.Offset(0, 1) = Date
ActiveCell.Offset(0, 2) = Time
ActiveCell.Offset(0, 3) = txtCompany.Value
ActiveCell.Offset(0, 4) = txtName.Value
ActiveCell.Offset(0, 5) = txtPhone.Value
ActiveCell.Offset(0, 6) = txtInvoiceNo.Value
ActiveCell.Offset(0, 7) = cmbInvoiceType.Value
ActiveCell.Offset(0, 8) = txtInvoiceDate.Value
ActiveCell.Offset(0, 9) = txtAmount.Value
ActiveCell.Offset(0, 10) = txtSubStartDate.Value
ActiveCell.Offset(0, 11) = txtWhichInvoice.Value
ActiveCell.Offset(0, 12) = txtPaid.Value

Select Case Late
Case opt30.Value Is Checked
ActiveCell.Offset(0, 13) = txtPaid.Value
Case opt60.Value Is Checked
ActiveCell.Offset(0, 14) = txtPaid.Value
Case opt90.Value Is Checked
ActiveCell.Offset(0, 15) = txtPaid.Value
Case opt120.Value Is Checked
ActiveCell.Offset(0, 16) = txtPaid.Value
Case opt121.Value Is Checked
ActiveCell.Offset(0, 17) = txtPaid.Value
End Select

ActiveCell.Offset(0, 18) = "Invoice Amount"
ActiveCell.Offset(0, 19) = "Amount 1"
ActiveCell.Offset(0, 20) = "Amount 1"
ActiveCell.Offset(0, 21) = txtComments.Value

Range("A1").Select
Call frmNewCollect_Initialize
End Sub


---
Message posted from http://www.ExcelForum.com/

Case Statement Help
 

Thanks a ton, it was an instant help!!!

--
Message posted from http://www.ExcelForum.com