if then else

P

Pam

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
 
B

Bob Phillips

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)
 
M

Mike H

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
 
P

Pam

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.
 
P

Pam

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
 
M

Mike H

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
 
B

Bob Phillips

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)
 
P

Pam

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.
 
P

Pam

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Entering data from a userform into multiple sheets 2
My macro doesn't work if sheet is protected but it works if not protected 2
Combobox will not show list 4
Runtime error 91: Object variable or With block variable not set 8
Duplicate declaration in scope 2
VBA to check omissions and show/hide form 0
Mutiple comboboxes on user form 1
Type mismatch error only on the first run 2

Top