Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case Statement Help
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Case Statement | Excel Discussion (Misc queries) | |||
Select Case Statement | Excel Worksheet Functions | |||
IF STATMENT OR CASE STATEMENT | Excel Discussion (Misc queries) | |||
Need help creating a case statement | Excel Programming | |||
Case statement | Excel Programming |