| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
if then else
I am trying to do a very simple statement, but vb6 keeps giving me errors on
this line. I want to search for the next available line (that is empty). Here's my code, what have I done wrong? It is giving me the error on the line after the If IsEmpty line. Thanks Pam Private Sub CmdSaveChanges_Click() ActiveWorkbook.Sheets("Order").Activate Range("A1").Select Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True ActiveCell.Value = TextDate.Value ActiveCell.Offset(0, 1) = ComboWebsite.Value If NewCust = True Then ActiveCell.Offset(0, 2).Value = "New Customer" ElseIf ExistCust = True Then ActiveCell.Offset(0, 2).Value = "Existing Customer" End If |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if then else
Try this
Private Sub CmdSaveChanges_Click() ActiveWorkbook.Sheets("Order").Activate With Range("A1").End(xlDown) .Offset(1, 0).Value = 123 'TextDate.Value .Offset(1, 1) = 456 'ComboWebsite.Value If NewCust Then .Offset(1, 2).Value = "New Customer" ElseIf ExistCust Then .Offset(1, 2).Value = "Existing Customer" End If End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Pam" wrote in message ... I am trying to do a very simple statement, but vb6 keeps giving me errors on this line. I want to search for the next available line (that is empty). Here's my code, what have I done wrong? It is giving me the error on the line after the If IsEmpty line. Thanks Pam Private Sub CmdSaveChanges_Click() ActiveWorkbook.Sheets("Order").Activate Range("A1").Select Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True ActiveCell.Value = TextDate.Value ActiveCell.Offset(0, 1) = ComboWebsite.Value If NewCust = True Then ActiveCell.Offset(0, 2).Value = "New Customer" ElseIf ExistCust = True Then ActiveCell.Offset(0, 2).Value = "Existing Customer" End If |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if then else
Pam
It finds the empty cell OK, if somewhat unconventionally, but is falling over on the line:- ActiveCell.Value = TextDate.Value I assume here you are trying to enter a date so this line will do that ActiveCell.Value = Date It falls over again on the line ActiveCell.Offset(0, 1) = ComboWebsite.Value and I don't really understand what your trying to do here. In the snippet of code given both Newcust and Existcust are not set so neither of the if or else conditions are satisfied. Mike "Pam" wrote: I am trying to do a very simple statement, but vb6 keeps giving me errors on this line. I want to search for the next available line (that is empty). Here's my code, what have I done wrong? It is giving me the error on the line after the If IsEmpty line. Thanks Pam Private Sub CmdSaveChanges_Click() ActiveWorkbook.Sheets("Order").Activate Range("A1").Select Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True ActiveCell.Value = TextDate.Value ActiveCell.Offset(0, 1) = ComboWebsite.Value If NewCust = True Then ActiveCell.Offset(0, 2).Value = "New Customer" ElseIf ExistCust = True Then ActiveCell.Offset(0, 2).Value = "Existing Customer" End If |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if then else
As you can see, I'm new to VB. I have a user form that the user enters data
into, when they click the command button save changes, I want the data to be transferred to the corresponding worksheet. I had it working until I closed it, now it won't work. "Mike H" wrote: Pam It finds the empty cell OK, if somewhat unconventionally, but is falling over on the line:- ActiveCell.Value = TextDate.Value I assume here you are trying to enter a date so this line will do that ActiveCell.Value = Date It falls over again on the line ActiveCell.Offset(0, 1) = ComboWebsite.Value and I don't really understand what your trying to do here. In the snippet of code given both Newcust and Existcust are not set so neither of the if or else conditions are satisfied. Mike "Pam" wrote: I am trying to do a very simple statement, but vb6 keeps giving me errors on this line. I want to search for the next available line (that is empty). Here's my code, what have I done wrong? It is giving me the error on the line after the If IsEmpty line. Thanks Pam Private Sub CmdSaveChanges_Click() ActiveWorkbook.Sheets("Order").Activate Range("A1").Select Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True ActiveCell.Value = TextDate.Value ActiveCell.Offset(0, 1) = ComboWebsite.Value If NewCust = True Then ActiveCell.Offset(0, 2).Value = "New Customer" ElseIf ExistCust = True Then ActiveCell.Offset(0, 2).Value = "Existing Customer" End If |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if then else
This is actually all of the code for that sub, didn't post it all because
it's so long, but maybe it will explain what I'm doing more. Private Sub CmdSaveChanges_Click() ActiveWorkbook.Sheets("Order").Activate Range("A1").Select Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True ActiveCell.Value = TextDate.Value ActiveCell.Offset(0, 1) = ComboWebsite.Value If NewCust = True Then ActiveCell.Offset(0, 2).Value = "New Customer" ElseIf ExistCust = True Then ActiveCell.Offset(0, 2).Value = "Existing Customer" End If If NewOrder = True Then ActiveCell.Offset(0, 3).Value = "New Order" ElseIf ReOrder = True Then ActiveCell.Offset(0, 3).Value = "ReOrder" End If ActiveCell.Offset(0, 4) = ContactName.Value ActiveCell.Offset(0, 5) = Email.Value ActiveCell.Offset(0, 6) = OldOrder.Value ActiveCell.Offset(0, 7) = Rep.Value ActiveCell.Offset(0, 8) = TxtBillName.Value ActiveCell.Offset(0, 9) = TxtBillAdd1.Value ActiveCell.Offset(0, 10) = TxtBillAdd2.Value ActiveCell.Offset(0, 11) = TxtBillCity.Value ActiveCell.Offset(0, 12) = TxtBillState.Value ActiveCell.Offset(0, 13) = TxtBillCountry.Value ActiveCell.Offset(0, 14) = TxtBillZip.Value ActiveCell.Offset(0, 15) = TxtShipAdd1.Value ActiveCell.Offset(0, 16) = TxtShipAdd2.Value ActiveCell.Offset(0, 17) = TxtShipCity.Value ActiveCell.Offset(0, 18) = TxtShipState.Value ActiveCell.Offset(0, 19) = TxtShipCountry.Value ActiveCell.Offset(0, 20) = TxtShipZip.Value ActiveCell.Offset(0, 21) = ComboCCType.Value ActiveCell.Offset(0, 22) = TextCC.Value ActiveCell.Offset(0, 23) = TextCCExp.Value ActiveCell.Offset(0, 24) = TextCCSec.Value ActiveCell.Offset(0, 25) = ComboItem1.Value ActiveCell.Offset(0, 26) = TxtItemQty1.Value ActiveCell.Offset(0, 27) = TxtItemUnit1.Value ActiveCell.Offset(0, 28) = TxtItemExt1.Value ActiveCell.Offset(0, 29) = ComboItem2.Value ActiveCell.Offset(0, 30) = TxtItemQty2.Value ActiveCell.Offset(0, 31) = TxtItemUnit2.Value ActiveCell.Offset(0, 32) = TxtItemExt2.Value ActiveCell.Offset(0, 33) = ComboItem3.Value ActiveCell.Offset(0, 34) = TxtItemQty3.Value ActiveCell.Offset(0, 35) = TxtItemUnit3.Value ActiveCell.Offset(0, 36) = TxtItemExt3.Value ActiveCell.Offset(0, 37) = ComboItem4.Value ActiveCell.Offset(0, 38) = TxtItemQty4.Value ActiveCell.Offset(0, 39) = TxtItemUnit4.Value ActiveCell.Offset(0, 40) = TxtItemExt4.Value ActiveCell.Offset(0, 41) = ShipExt.Value ActiveCell.Offset(0, 42) = SetupExt.Value ActiveCell.Offset(0, 43) = DesignExt.Value ActiveCell.Offset(0, 44) = RushExt.Value ActiveCell.Offset(0, 45) = Colors.Value ActiveCell.Offset(0, 46) = ComboAttach.Value ActiveCell.Offset(0, 47) = ComboMaterial.Value ActiveCell.Offset(0, 48) = ComboType.Value ActiveCell.Offset(0, 49) = NumberOColors.Value ActiveCell.Offset(0, 50) = instructions.Value ActiveCell.Offset(0, 51) = ComboAttach.Value ActiveCell.Offset(0, 52) = Factory.Value ActiveCell.Offset(0, 53) = Artfile.Value ActiveCell.Offset(0, 54) = ComboShip.Value End Sub "Mike H" wrote: Pam It finds the empty cell OK, if somewhat unconventionally, but is falling over on the line:- ActiveCell.Value = TextDate.Value I assume here you are trying to enter a date so this line will do that ActiveCell.Value = Date It falls over again on the line ActiveCell.Offset(0, 1) = ComboWebsite.Value and I don't really understand what your trying to do here. In the snippet of code given both Newcust and Existcust are not set so neither of the if or else conditions are satisfied. Mike "Pam" wrote: I am trying to do a very simple statement, but vb6 keeps giving me errors on this line. I want to search for the next available line (that is empty). Here's my code, what have I done wrong? It is giving me the error on the line after the If IsEmpty line. Thanks Pam Private Sub CmdSaveChanges_Click() ActiveWorkbook.Sheets("Order").Activate Range("A1").Select Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True ActiveCell.Value = TextDate.Value ActiveCell.Offset(0, 1) = ComboWebsite.Value If NewCust = True Then ActiveCell.Offset(0, 2).Value = "New Customer" ElseIf ExistCust = True Then ActiveCell.Offset(0, 2).Value = "Existing Customer" End If |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if then else
Pam,
You method of finding the first empty cell is unconventional but it works and on the assumption that you have textboxes/checkboxes on your userform that your code refers to then I can see no other reason why you code should fail. What precisely is the error? Mike "Pam" wrote: This is actually all of the code for that sub, didn't post it all because it's so long, but maybe it will explain what I'm doing more. Private Sub CmdSaveChanges_Click() ActiveWorkbook.Sheets("Order").Activate Range("A1").Select Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True ActiveCell.Value = TextDate.Value ActiveCell.Offset(0, 1) = ComboWebsite.Value If NewCust = True Then ActiveCell.Offset(0, 2).Value = "New Customer" ElseIf ExistCust = True Then ActiveCell.Offset(0, 2).Value = "Existing Customer" End If If NewOrder = True Then ActiveCell.Offset(0, 3).Value = "New Order" ElseIf ReOrder = True Then ActiveCell.Offset(0, 3).Value = "ReOrder" End If ActiveCell.Offset(0, 4) = ContactName.Value ActiveCell.Offset(0, 5) = Email.Value ActiveCell.Offset(0, 6) = OldOrder.Value ActiveCell.Offset(0, 7) = Rep.Value ActiveCell.Offset(0, 8) = TxtBillName.Value ActiveCell.Offset(0, 9) = TxtBillAdd1.Value ActiveCell.Offset(0, 10) = TxtBillAdd2.Value ActiveCell.Offset(0, 11) = TxtBillCity.Value ActiveCell.Offset(0, 12) = TxtBillState.Value ActiveCell.Offset(0, 13) = TxtBillCountry.Value ActiveCell.Offset(0, 14) = TxtBillZip.Value ActiveCell.Offset(0, 15) = TxtShipAdd1.Value ActiveCell.Offset(0, 16) = TxtShipAdd2.Value ActiveCell.Offset(0, 17) = TxtShipCity.Value ActiveCell.Offset(0, 18) = TxtShipState.Value ActiveCell.Offset(0, 19) = TxtShipCountry.Value ActiveCell.Offset(0, 20) = TxtShipZip.Value ActiveCell.Offset(0, 21) = ComboCCType.Value ActiveCell.Offset(0, 22) = TextCC.Value ActiveCell.Offset(0, 23) = TextCCExp.Value ActiveCell.Offset(0, 24) = TextCCSec.Value ActiveCell.Offset(0, 25) = ComboItem1.Value ActiveCell.Offset(0, 26) = TxtItemQty1.Value ActiveCell.Offset(0, 27) = TxtItemUnit1.Value ActiveCell.Offset(0, 28) = TxtItemExt1.Value ActiveCell.Offset(0, 29) = ComboItem2.Value ActiveCell.Offset(0, 30) = TxtItemQty2.Value ActiveCell.Offset(0, 31) = TxtItemUnit2.Value ActiveCell.Offset(0, 32) = TxtItemExt2.Value ActiveCell.Offset(0, 33) = ComboItem3.Value ActiveCell.Offset(0, 34) = TxtItemQty3.Value ActiveCell.Offset(0, 35) = TxtItemUnit3.Value ActiveCell.Offset(0, 36) = TxtItemExt3.Value ActiveCell.Offset(0, 37) = ComboItem4.Value ActiveCell.Offset(0, 38) = TxtItemQty4.Value ActiveCell.Offset(0, 39) = TxtItemUnit4.Value ActiveCell.Offset(0, 40) = TxtItemExt4.Value ActiveCell.Offset(0, 41) = ShipExt.Value ActiveCell.Offset(0, 42) = SetupExt.Value ActiveCell.Offset(0, 43) = DesignExt.Value ActiveCell.Offset(0, 44) = RushExt.Value ActiveCell.Offset(0, 45) = Colors.Value ActiveCell.Offset(0, 46) = ComboAttach.Value ActiveCell.Offset(0, 47) = ComboMaterial.Value ActiveCell.Offset(0, 48) = ComboType.Value ActiveCell.Offset(0, 49) = NumberOColors.Value ActiveCell.Offset(0, 50) = instructions.Value ActiveCell.Offset(0, 51) = ComboAttach.Value ActiveCell.Offset(0, 52) = Factory.Value ActiveCell.Offset(0, 53) = Artfile.Value ActiveCell.Offset(0, 54) = ComboShip.Value End Sub "Mike H" wrote: Pam It finds the empty cell OK, if somewhat unconventionally, but is falling over on the line:- ActiveCell.Value = TextDate.Value I assume here you are trying to enter a date so this line will do that ActiveCell.Value = Date It falls over again on the line ActiveCell.Offset(0, 1) = ComboWebsite.Value and I don't really understand what your trying to do here. In the snippet of code given both Newcust and Existcust are not set so neither of the if or else conditions are satisfied. Mike "Pam" wrote: I am trying to do a very simple statement, but vb6 keeps giving me errors on this line. I want to search for the next available line (that is empty). Here's my code, what have I done wrong? It is giving me the error on the line after the If IsEmpty line. Thanks Pam Private Sub CmdSaveChanges_Click() ActiveWorkbook.Sheets("Order").Activate Range("A1").Select Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True ActiveCell.Value = TextDate.Value ActiveCell.Offset(0, 1) = ComboWebsite.Value If NewCust = True Then ActiveCell.Offset(0, 2).Value = "New Customer" ElseIf ExistCust = True Then ActiveCell.Offset(0, 2).Value = "Existing Customer" End If |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if then else
Try again
Private Sub CmdSaveChanges_Click() ActiveWorkbook.Sheets("Order").Activate With Range("A1").End(xlDown) .Offset(1, 0).Value = TextDate.Value .Offset(1, 1) = ComboWebsite.Value If NewCust Then .Offset(1, 2).Value = "New Customer" ElseIf ExistCust Then .Offset(1, 2).Value = "Existing Customer" End If If NewOrder Then .Offset(1, 3).Value = "New Order" ElseIf ReOrder = True Then .Offset(1, 3).Value = "ReOrder" End If .Offset(1, 4) = ContactName.Value .Offset(1, 5) = Email.Value .Offset(1, 6) = OldOrder.Value .Offset(1, 7) = Rep.Value .Offset(1, 8) = TxtBillName.Value .Offset(1, 9) = TxtBillAdd1.Value .Offset(1, 10) = TxtBillAdd2.Value .Offset(1, 11) = TxtBillCity.Value .Offset(1, 12) = TxtBillState.Value .Offset(1, 13) = TxtBillCountry.Value .Offset(1, 14) = TxtBillZip.Value .Offset(1, 15) = TxtShipAdd1.Value .Offset(1, 16) = TxtShipAdd2.Value .Offset(1, 17) = TxtShipCity.Value .Offset(1, 18) = TxtShipState.Value .Offset(1, 19) = TxtShipCountry.Value .Offset(1, 20) = TxtShipZip.Value .Offset(1, 21) = ComboCCType.Value .Offset(1, 22) = TextCC.Value .Offset(1, 23) = TextCCExp.Value .Offset(1, 24) = TextCCSec.Value .Offset(1, 25) = ComboItem1.Value .Offset(1, 26) = TxtItemQty1.Value .Offset(1, 27) = TxtItemUnit1.Value .Offset(1, 28) = TxtItemExt1.Value .Offset(1, 29) = ComboItem2.Value .Offset(1, 30) = TxtItemQty2.Value .Offset(1, 31) = TxtItemUnit2.Value .Offset(1, 32) = TxtItemExt2.Value .Offset(1, 33) = ComboItem3.Value .Offset(1, 34) = TxtItemQty3.Value .Offset(1, 35) = TxtItemUnit3.Value .Offset(1, 36) = TxtItemExt3.Value .Offset(1, 37) = ComboItem4.Value .Offset(1, 38) = TxtItemQty4.Value .Offset(1, 39) = TxtItemUnit4.Value .Offset(1, 40) = TxtItemExt4.Value .Offset(1, 41) = ShipExt.Value .Offset(1, 42) = SetupExt.Value .Offset(1, 43) = DesignExt.Value .Offset(1, 44) = RushExt.Value .Offset(1, 45) = Colors.Value .Offset(1, 46) = ComboAttach.Value .Offset(1, 47) = ComboMaterial.Value .Offset(1, 48) = ComboType.Value .Offset(1, 49) = NumberOColors.Value .Offset(1, 50) = instructions.Value .Offset(1, 51) = ComboAttach.Value .Offset(1, 52) = Factory.Value .Offset(1, 53) = Artfile.Value .Offset(1, 54) = ComboShip.Value End With End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Pam" wrote in message ... This is actually all of the code for that sub, didn't post it all because it's so long, but maybe it will explain what I'm doing more. Private Sub CmdSaveChanges_Click() ActiveWorkbook.Sheets("Order").Activate Range("A1").Select Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True ActiveCell.Value = TextDate.Value ActiveCell.Offset(0, 1) = ComboWebsite.Value If NewCust = True Then ActiveCell.Offset(0, 2).Value = "New Customer" ElseIf ExistCust = True Then ActiveCell.Offset(0, 2).Value = "Existing Customer" End If If NewOrder = True Then ActiveCell.Offset(0, 3).Value = "New Order" ElseIf ReOrder = True Then ActiveCell.Offset(0, 3).Value = "ReOrder" End If ActiveCell.Offset(0, 4) = ContactName.Value ActiveCell.Offset(0, 5) = Email.Value ActiveCell.Offset(0, 6) = OldOrder.Value ActiveCell.Offset(0, 7) = Rep.Value ActiveCell.Offset(0, 8) = TxtBillName.Value ActiveCell.Offset(0, 9) = TxtBillAdd1.Value ActiveCell.Offset(0, 10) = TxtBillAdd2.Value ActiveCell.Offset(0, 11) = TxtBillCity.Value ActiveCell.Offset(0, 12) = TxtBillState.Value ActiveCell.Offset(0, 13) = TxtBillCountry.Value ActiveCell.Offset(0, 14) = TxtBillZip.Value ActiveCell.Offset(0, 15) = TxtShipAdd1.Value ActiveCell.Offset(0, 16) = TxtShipAdd2.Value ActiveCell.Offset(0, 17) = TxtShipCity.Value ActiveCell.Offset(0, 18) = TxtShipState.Value ActiveCell.Offset(0, 19) = TxtShipCountry.Value ActiveCell.Offset(0, 20) = TxtShipZip.Value ActiveCell.Offset(0, 21) = ComboCCType.Value ActiveCell.Offset(0, 22) = TextCC.Value ActiveCell.Offset(0, 23) = TextCCExp.Value ActiveCell.Offset(0, 24) = TextCCSec.Value ActiveCell.Offset(0, 25) = ComboItem1.Value ActiveCell.Offset(0, 26) = TxtItemQty1.Value ActiveCell.Offset(0, 27) = TxtItemUnit1.Value ActiveCell.Offset(0, 28) = TxtItemExt1.Value ActiveCell.Offset(0, 29) = ComboItem2.Value ActiveCell.Offset(0, 30) = TxtItemQty2.Value ActiveCell.Offset(0, 31) = TxtItemUnit2.Value ActiveCell.Offset(0, 32) = TxtItemExt2.Value ActiveCell.Offset(0, 33) = ComboItem3.Value ActiveCell.Offset(0, 34) = TxtItemQty3.Value ActiveCell.Offset(0, 35) = TxtItemUnit3.Value ActiveCell.Offset(0, 36) = TxtItemExt3.Value ActiveCell.Offset(0, 37) = ComboItem4.Value ActiveCell.Offset(0, 38) = TxtItemQty4.Value ActiveCell.Offset(0, 39) = TxtItemUnit4.Value ActiveCell.Offset(0, 40) = TxtItemExt4.Value ActiveCell.Offset(0, 41) = ShipExt.Value ActiveCell.Offset(0, 42) = SetupExt.Value ActiveCell.Offset(0, 43) = DesignExt.Value ActiveCell.Offset(0, 44) = RushExt.Value ActiveCell.Offset(0, 45) = Colors.Value ActiveCell.Offset(0, 46) = ComboAttach.Value ActiveCell.Offset(0, 47) = ComboMaterial.Value ActiveCell.Offset(0, 48) = ComboType.Value ActiveCell.Offset(0, 49) = NumberOColors.Value ActiveCell.Offset(0, 50) = instructions.Value ActiveCell.Offset(0, 51) = ComboAttach.Value ActiveCell.Offset(0, 52) = Factory.Value ActiveCell.Offset(0, 53) = Artfile.Value ActiveCell.Offset(0, 54) = ComboShip.Value End Sub "Mike H" wrote: Pam It finds the empty cell OK, if somewhat unconventionally, but is falling over on the line:- ActiveCell.Value = TextDate.Value I assume here you are trying to enter a date so this line will do that ActiveCell.Value = Date It falls over again on the line ActiveCell.Offset(0, 1) = ComboWebsite.Value and I don't really understand what your trying to do here. In the snippet of code given both Newcust and Existcust are not set so neither of the if or else conditions are satisfied. Mike "Pam" wrote: I am trying to do a very simple statement, but vb6 keeps giving me errors on this line. I want to search for the next available line (that is empty). Here's my code, what have I done wrong? It is giving me the error on the line after the If IsEmpty line. Thanks Pam Private Sub CmdSaveChanges_Click() ActiveWorkbook.Sheets("Order").Activate Range("A1").Select Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True ActiveCell.Value = TextDate.Value ActiveCell.Offset(0, 1) = ComboWebsite.Value If NewCust = True Then ActiveCell.Offset(0, 2).Value = "New Customer" ElseIf ExistCust = True Then ActiveCell.Offset(0, 2).Value = "Existing Customer" End If |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if then else
Thank you, I can see the logic behind that and it cuts down the code, but now
the sheet is giving me a problem on Sub OpenLanyardVBA() form.Show End Sub I have changed the name from OrderForm to UserForm and finally to form to see if it was a naming problem, but it doesn't seem to be. The error I get is Run-Time error '424' Object required. Excel did crash on me while I was working on this, and I got all kinds of errors saying file not found, but I restarted everything and the errors went away. Thanks for your help. "Bob Phillips" wrote: Try again Private Sub CmdSaveChanges_Click() ActiveWorkbook.Sheets("Order").Activate With Range("A1").End(xlDown) .Offset(1, 0).Value = TextDate.Value .Offset(1, 1) = ComboWebsite.Value If NewCust Then .Offset(1, 2).Value = "New Customer" ElseIf ExistCust Then .Offset(1, 2).Value = "Existing Customer" End If If NewOrder Then .Offset(1, 3).Value = "New Order" ElseIf ReOrder = True Then .Offset(1, 3).Value = "ReOrder" End If .Offset(1, 4) = ContactName.Value .Offset(1, 5) = Email.Value .Offset(1, 6) = OldOrder.Value .Offset(1, 7) = Rep.Value .Offset(1, 8) = TxtBillName.Value .Offset(1, 9) = TxtBillAdd1.Value .Offset(1, 10) = TxtBillAdd2.Value .Offset(1, 11) = TxtBillCity.Value .Offset(1, 12) = TxtBillState.Value .Offset(1, 13) = TxtBillCountry.Value .Offset(1, 14) = TxtBillZip.Value .Offset(1, 15) = TxtShipAdd1.Value .Offset(1, 16) = TxtShipAdd2.Value .Offset(1, 17) = TxtShipCity.Value .Offset(1, 18) = TxtShipState.Value .Offset(1, 19) = TxtShipCountry.Value .Offset(1, 20) = TxtShipZip.Value .Offset(1, 21) = ComboCCType.Value .Offset(1, 22) = TextCC.Value .Offset(1, 23) = TextCCExp.Value .Offset(1, 24) = TextCCSec.Value .Offset(1, 25) = ComboItem1.Value .Offset(1, 26) = TxtItemQty1.Value .Offset(1, 27) = TxtItemUnit1.Value .Offset(1, 28) = TxtItemExt1.Value .Offset(1, 29) = ComboItem2.Value .Offset(1, 30) = TxtItemQty2.Value .Offset(1, 31) = TxtItemUnit2.Value .Offset(1, 32) = TxtItemExt2.Value .Offset(1, 33) = ComboItem3.Value .Offset(1, 34) = TxtItemQty3.Value .Offset(1, 35) = TxtItemUnit3.Value .Offset(1, 36) = TxtItemExt3.Value .Offset(1, 37) = ComboItem4.Value .Offset(1, 38) = TxtItemQty4.Value .Offset(1, 39) = TxtItemUnit4.Value .Offset(1, 40) = TxtItemExt4.Value .Offset(1, 41) = ShipExt.Value .Offset(1, 42) = SetupExt.Value .Offset(1, 43) = DesignExt.Value .Offset(1, 44) = RushExt.Value .Offset(1, 45) = Colors.Value .Offset(1, 46) = ComboAttach.Value .Offset(1, 47) = ComboMaterial.Value .Offset(1, 48) = ComboType.Value .Offset(1, 49) = NumberOColors.Value .Offset(1, 50) = instructions.Value .Offset(1, 51) = ComboAttach.Value .Offset(1, 52) = Factory.Value .Offset(1, 53) = Artfile.Value .Offset(1, 54) = ComboShip.Value End With End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Pam" wrote in message ... This is actually all of the code for that sub, didn't post it all because it's so long, but maybe it will explain what I'm doing more. Private Sub CmdSaveChanges_Click() ActiveWorkbook.Sheets("Order").Activate Range("A1").Select Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True ActiveCell.Value = TextDate.Value ActiveCell.Offset(0, 1) = ComboWebsite.Value If NewCust = True Then ActiveCell.Offset(0, 2).Value = "New Customer" ElseIf ExistCust = True Then ActiveCell.Offset(0, 2).Value = "Existing Customer" End If If NewOrder = True Then ActiveCell.Offset(0, 3).Value = "New Order" ElseIf ReOrder = True Then ActiveCell.Offset(0, 3).Value = "ReOrder" End If ActiveCell.Offset(0, 4) = ContactName.Value ActiveCell.Offset(0, 5) = Email.Value ActiveCell.Offset(0, 6) = OldOrder.Value ActiveCell.Offset(0, 7) = Rep.Value ActiveCell.Offset(0, 8) = TxtBillName.Value ActiveCell.Offset(0, 9) = TxtBillAdd1.Value ActiveCell.Offset(0, 10) = TxtBillAdd2.Value ActiveCell.Offset(0, 11) = TxtBillCity.Value ActiveCell.Offset(0, 12) = TxtBillState.Value ActiveCell.Offset(0, 13) = TxtBillCountry.Value ActiveCell.Offset(0, 14) = TxtBillZip.Value ActiveCell.Offset(0, 15) = TxtShipAdd1.Value ActiveCell.Offset(0, 16) = TxtShipAdd2.Value ActiveCell.Offset(0, 17) = TxtShipCity.Value ActiveCell.Offset(0, 18) = TxtShipState.Value ActiveCell.Offset(0, 19) = TxtShipCountry.Value ActiveCell.Offset(0, 20) = TxtShipZip.Value ActiveCell.Offset(0, 21) = ComboCCType.Value ActiveCell.Offset(0, 22) = TextCC.Value ActiveCell.Offset(0, 23) = TextCCExp.Value ActiveCell.Offset(0, 24) = TextCCSec.Value ActiveCell.Offset(0, 25) = ComboItem1.Value ActiveCell.Offset(0, 26) = TxtItemQty1.Value ActiveCell.Offset(0, 27) = TxtItemUnit1.Value ActiveCell.Offset(0, 28) = TxtItemExt1.Value ActiveCell.Offset(0, 29) = ComboItem2.Value ActiveCell.Offset(0, 30) = TxtItemQty2.Value ActiveCell.Offset(0, 31) = TxtItemUnit2.Value ActiveCell.Offset(0, 32) = TxtItemExt2.Value ActiveCell.Offset(0, 33) = ComboItem3.Value ActiveCell.Offset(0, 34) = TxtItemQty3.Value ActiveCell.Offset(0, 35) = TxtItemUnit3.Value ActiveCell.Offset(0, 36) = TxtItemExt3.Value ActiveCell.Offset(0, 37) = ComboItem4.Value ActiveCell.Offset(0, 38) = TxtItemQty4.Value ActiveCell.Offset(0, 39) = TxtItemUnit4.Value ActiveCell.Offset(0, 40) = TxtItemExt4.Value ActiveCell.Offset(0, 41) = ShipExt.Value ActiveCell.Offset(0, 42) = SetupExt.Value ActiveCell.Offset(0, 43) = DesignExt.Value ActiveCell.Offset(0, 44) = RushExt.Value ActiveCell.Offset(0, 45) = Colors.Value ActiveCell.Offset(0, 46) = ComboAttach.Value ActiveCell.Offset(0, 47) = ComboMaterial.Value ActiveCell.Offset(0, 48) = ComboType.Value ActiveCell.Offset(0, 49) = NumberOColors.Value ActiveCell.Offset(0, 50) = instructions.Value ActiveCell.Offset(0, 51) = ComboAttach.Value ActiveCell.Offset(0, 52) = Factory.Value ActiveCell.Offset(0, 53) = Artfile.Value ActiveCell.Offset(0, 54) = ComboShip.Value End Sub "Mike H" wrote: Pam It finds the empty cell OK, if somewhat unconventionally, but is falling over on the line:- ActiveCell.Value = TextDate.Value I assume here you are trying to enter a date so this line will do that ActiveCell.Value = Date It falls over again on the line ActiveCell.Offset(0, 1) = ComboWebsite.Value and I don't really understand what your trying to do here. In the snippet of code given both Newcust and Existcust are not set so neither of the if or else conditions are satisfied. Mike "Pam" wrote: I am trying to do a very simple statement, but vb6 keeps giving me errors on this line. I want to search for the next available line (that is empty). Here's my code, what have I done wrong? It is giving me the error on the line after the If IsEmpty line. Thanks Pam Private Sub CmdSaveChanges_Click() ActiveWorkbook.Sheets("Order").Activate Range("A1").Select Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True ActiveCell.Value = TextDate.Value ActiveCell.Offset(0, 1) = ComboWebsite.Value If NewCust = True Then ActiveCell.Offset(0, 2).Value = "New Customer" ElseIf ExistCust = True Then ActiveCell.Offset(0, 2).Value = "Existing Customer" End If |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if then else
I got it to work, thank you, I had to create a new workbook with a new form,
so it must have been the error that I received when excel crashed. Thank you both! "Bob Phillips" wrote: Try again Private Sub CmdSaveChanges_Click() ActiveWorkbook.Sheets("Order").Activate With Range("A1").End(xlDown) .Offset(1, 0).Value = TextDate.Value .Offset(1, 1) = ComboWebsite.Value If NewCust Then .Offset(1, 2).Value = "New Customer" ElseIf ExistCust Then .Offset(1, 2).Value = "Existing Customer" End If If NewOrder Then .Offset(1, 3).Value = "New Order" ElseIf ReOrder = True Then .Offset(1, 3).Value = "ReOrder" End If .Offset(1, 4) = ContactName.Value .Offset(1, 5) = Email.Value .Offset(1, 6) = OldOrder.Value .Offset(1, 7) = Rep.Value .Offset(1, 8) = TxtBillName.Value .Offset(1, 9) = TxtBillAdd1.Value .Offset(1, 10) = TxtBillAdd2.Value .Offset(1, 11) = TxtBillCity.Value .Offset(1, 12) = TxtBillState.Value .Offset(1, 13) = TxtBillCountry.Value .Offset(1, 14) = TxtBillZip.Value .Offset(1, 15) = TxtShipAdd1.Value .Offset(1, 16) = TxtShipAdd2.Value .Offset(1, 17) = TxtShipCity.Value .Offset(1, 18) = TxtShipState.Value .Offset(1, 19) = TxtShipCountry.Value .Offset(1, 20) = TxtShipZip.Value .Offset(1, 21) = ComboCCType.Value .Offset(1, 22) = TextCC.Value .Offset(1, 23) = TextCCExp.Value .Offset(1, 24) = TextCCSec.Value .Offset(1, 25) = ComboItem1.Value .Offset(1, 26) = TxtItemQty1.Value .Offset(1, 27) = TxtItemUnit1.Value .Offset(1, 28) = TxtItemExt1.Value .Offset(1, 29) = ComboItem2.Value .Offset(1, 30) = TxtItemQty2.Value .Offset(1, 31) = TxtItemUnit2.Value .Offset(1, 32) = TxtItemExt2.Value .Offset(1, 33) = ComboItem3.Value .Offset(1, 34) = TxtItemQty3.Value .Offset(1, 35) = TxtItemUnit3.Value .Offset(1, 36) = TxtItemExt3.Value .Offset(1, 37) = ComboItem4.Value .Offset(1, 38) = TxtItemQty4.Value .Offset(1, 39) = TxtItemUnit4.Value .Offset(1, 40) = TxtItemExt4.Value .Offset(1, 41) = ShipExt.Value .Offset(1, 42) = SetupExt.Value .Offset(1, 43) = DesignExt.Value .Offset(1, 44) = RushExt.Value .Offset(1, 45) = Colors.Value .Offset(1, 46) = ComboAttach.Value .Offset(1, 47) = ComboMaterial.Value .Offset(1, 48) = ComboType.Value .Offset(1, 49) = NumberOColors.Value .Offset(1, 50) = instructions.Value .Offset(1, 51) = ComboAttach.Value .Offset(1, 52) = Factory.Value .Offset(1, 53) = Artfile.Value .Offset(1, 54) = ComboShip.Value End With End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Pam" wrote in message ... This is actually all of the code for that sub, didn't post it all because it's so long, but maybe it will explain what I'm doing more. Private Sub CmdSaveChanges_Click() ActiveWorkbook.Sheets("Order").Activate Range("A1").Select Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True ActiveCell.Value = TextDate.Value ActiveCell.Offset(0, 1) = ComboWebsite.Value If NewCust = True Then ActiveCell.Offset(0, 2).Value = "New Customer" ElseIf ExistCust = True Then ActiveCell.Offset(0, 2).Value = "Existing Customer" End If If NewOrder = True Then ActiveCell.Offset(0, 3).Value = "New Order" ElseIf ReOrder = True Then ActiveCell.Offset(0, 3).Value = "ReOrder" End If ActiveCell.Offset(0, 4) = ContactName.Value ActiveCell.Offset(0, 5) = Email.Value ActiveCell.Offset(0, 6) = OldOrder.Value ActiveCell.Offset(0, 7) = Rep.Value ActiveCell.Offset(0, 8) = TxtBillName.Value ActiveCell.Offset(0, 9) = TxtBillAdd1.Value ActiveCell.Offset(0, 10) = TxtBillAdd2.Value ActiveCell.Offset(0, 11) = TxtBillCity.Value ActiveCell.Offset(0, 12) = TxtBillState.Value ActiveCell.Offset(0, 13) = TxtBillCountry.Value ActiveCell.Offset(0, 14) = TxtBillZip.Value ActiveCell.Offset(0, 15) = TxtShipAdd1.Value ActiveCell.Offset(0, 16) = TxtShipAdd2.Value ActiveCell.Offset(0, 17) = TxtShipCity.Value ActiveCell.Offset(0, 18) = TxtShipState.Value ActiveCell.Offset(0, 19) = TxtShipCountry.Value ActiveCell.Offset(0, 20) = TxtShipZip.Value ActiveCell.Offset(0, 21) = ComboCCType.Value ActiveCell.Offset(0, 22) = TextCC.Value ActiveCell.Offset(0, 23) = TextCCExp.Value ActiveCell.Offset(0, 24) = TextCCSec.Value ActiveCell.Offset(0, 25) = ComboItem1.Value ActiveCell.Offset(0, 26) = TxtItemQty1.Value ActiveCell.Offset(0, 27) = TxtItemUnit1.Value ActiveCell.Offset(0, 28) = TxtItemExt1.Value ActiveCell.Offset(0, 29) = ComboItem2.Value ActiveCell.Offset(0, 30) = TxtItemQty2.Value ActiveCell.Offset(0, 31) = TxtItemUnit2.Value ActiveCell.Offset(0, 32) = TxtItemExt2.Value ActiveCell.Offset(0, 33) = ComboItem3.Value ActiveCell.Offset(0, 34) = TxtItemQty3.Value ActiveCell.Offset(0, 35) = TxtItemUnit3.Value ActiveCell.Offset(0, 36) = TxtItemExt3.Value ActiveCell.Offset(0, 37) = ComboItem4.Value ActiveCell.Offset(0, 38) = TxtItemQty4.Value ActiveCell.Offset(0, 39) = TxtItemUnit4.Value ActiveCell.Offset(0, 40) = TxtItemExt4.Value ActiveCell.Offset(0, 41) = ShipExt.Value ActiveCell.Offset(0, 42) = SetupExt.Value ActiveCell.Offset(0, 43) = DesignExt.Value ActiveCell.Offset(0, 44) = RushExt.Value ActiveCell.Offset(0, 45) = Colors.Value ActiveCell.Offset(0, 46) = ComboAttach.Value ActiveCell.Offset(0, 47) = ComboMaterial.Value ActiveCell.Offset(0, 48) = ComboType.Value ActiveCell.Offset(0, 49) = NumberOColors.Value ActiveCell.Offset(0, 50) = instructions.Value ActiveCell.Offset(0, 51) = ComboAttach.Value ActiveCell.Offset(0, 52) = Factory.Value ActiveCell.Offset(0, 53) = Artfile.Value ActiveCell.Offset(0, 54) = ComboShip.Value End Sub "Mike H" wrote: Pam It finds the empty cell OK, if somewhat unconventionally, but is falling over on the line:- ActiveCell.Value = TextDate.Value I assume here you are trying to enter a date so this line will do that ActiveCell.Value = Date It falls over again on the line ActiveCell.Offset(0, 1) = ComboWebsite.Value and I don't really understand what your trying to do here. In the snippet of code given both Newcust and Existcust are not set so neither of the if or else conditions are satisfied. Mike "Pam" wrote: I am trying to do a very simple statement, but vb6 keeps giving me errors on this line. I want to search for the next available line (that is empty). Here's my code, what have I done wrong? It is giving me the error on the line after the If IsEmpty line. Thanks Pam Private Sub CmdSaveChanges_Click() ActiveWorkbook.Sheets("Order").Activate Range("A1").Select Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True ActiveCell.Value = TextDate.Value ActiveCell.Offset(0, 1) = ComboWebsite.Value If NewCust = True Then ActiveCell.Offset(0, 2).Value = "New Customer" ElseIf ExistCust = True Then ActiveCell.Offset(0, 2).Value = "Existing Customer" End If |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
