Completion of Then Statement

[Kev]

Sub CustAllocInput_AutoShape1_Click()
UserForm1.Show
Sheets("CustAllocInput").Select <---------HERE
Range("CustList1").Select
End Sub
*****************************************
Private Sub cmdOk_Click()
CustName = UserForm1.CustName
If CustName = "" Then End??????
Set Customers = Sheets("Customer List").Range
("Customers")
If WorksheetFunction.CountIf(Customers, CustName) > 0
Then End?????
With Sheets("Customer List")
clastrow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("A" & clastrow + 1).Value = CustName
.Range("Customers").Cells(1, 1).Resize(clastrow +
1, Range("Customers").Columns.Count).Name = "Customers"
End With
Unload Me
Sheets("Customer List").Select
Selection.Sort Key1:=Range("Customers"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

If cmdOK routine runs all the way I return to <---- HERE
in my starting code block. However the two Then End?????
statements do not? What have I overlooked???
Thanks to all once again.....
Kev.

 

[JulieD]

Hi Kev

try exit sub instead of "then end"

Cheers
JulieD

 

[Kev]

Does not seem to like only "exit sub" in combination to
IF statement. In first instance, syntax error "Expectd
end of Statement" and second instance "Expected Then or
Goto".
Have I misinterpreted somewhere???
Thanks...
Kev.

 

[JulieD]

Hi Kev

this seems to work for me
(do you really mean greater than here anyway?)

and with the CustName = UserForm1.CustName one, have you put a breakpoint on
the line to see what is actually returned if you leave it blank ... i often
find using code like below is a more reliable way of checking:

If LEN(CustName) < 1 Then Exit Sub

Hope this helps, somewhat

Cheers
JulieD

 

[Kev]

Removed and then re-entered and no problems with syntax
any more.
Both of these conditions leave the userform on the screen
and active cell is whichever, where I have it if the
procedure finishes,it goes back to the <---HERE arrow and
userform is off the screen and a specific cell containing
a drop down list then is chosen.
How or what do you mean by a breakpoint?? - I had that
line of code to overcome a blank entry occuring in my
customer list range.
Thanks for your ongoing assistance and I will take any
code improvements/tips that are offered my way....
Kev

 

[JulieD]

Hi Kev

you'll need to explicitly close the userform e.g.
If WorksheetFunction.CountIf(Customers, CustName) Then
unload me
'whatever else
Exit Sub
end if

a break point is created by pressing F9 on a line of code, the code will
then run through to that line and stop .. allowing you to check values &
variables at that point .. you can then run the code line by line F8
(placing your cursor over variables will give you their values) or run the
code to the next breakpoint or end of the code by using F5 .. it's a good
way of checking what is actually going on in the code.

Cheers
JulieD

 

[Kev]

Hi Julie,
There are 2 parts to the code - 1. userform (private sub)
and 2.part of mudule two. When the code Exit Sub in
UserForm code, it stops there when it should return to
the <---- HERE section of the starting code in Module 2.
Copy of UserForm code as it stands follows. If UserForm
code is followed without an early exit macro goes back to
Module 2 code as expected. Trying to have consistency in
my procedures.
Private Sub cmdOk_Click()
CustName = UserForm1.CustName
If Len(CustName) < 1 Then Exit Sub
Set Customers = Sheets("Customer List").Range
("Customers")
If WorksheetFunction.CountIf(Customers, CustName) > 0
Then Exit Sub
With Sheets("Customer List")
clastrow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("A" & clastrow + 1).Value = CustName
.Range("Customers").Cells(1, 1).Resize(clastrow +
1, Range("Customers").Columns.Count).Name = "Customers"
End With
Unload Me
Sheets("Customer List").Select
Selection.Sort Key1:=Range("Customers"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

Thanks a million......Kev.