Exit Sub???

[Kev]

Code from my UserForm
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
******************************************
Code from AutoShape macro to start UserForm
Sub CustAllocInput_AutoShape1_Click()
UserForm1.Show
UserForm1.Hide
Sheets("CustAllocInput").Select
Range("CustList1").Select
End Sub
*******************************************
If the UserForm code completes all the way through the
last two lines in the AutoShape macro are performed.
However if either of the two EXIT SUB conditions occur
the user form stays on the screen and the code goes no
further. I want for each EXIT SUB condition, the code to
complete the last two lines of the AutoShape macro.
Where is my error and the fix please?
Thanks a million....
Kev.

 

[JulieD]

Hi Kev

in my last reply on the last thread i said
----
you'll need to explicitly close the userform e.g.
If WorksheetFunction.CountIf(Customers, CustName) Then
unload me
Exit Sub
end if
---
the unload code needs to go infront of both Exit Subs as above

this will return you to the AutoShape macro and complete the lines there

Cheers
JulieD

 

[Kev]

I am stumped - I keep getting errors like "expected end
of statement" "Block if without end if". I have tried
all I can think of but without success. I obviously am
missing something in how the "If...Then...End If"
component is to be used. I have no other test statements
to add into the code. An adjustment to this code for a
working result would be appreciated...
Kev.

 

[JulieD]

Hi Kev

try (lines changed marked with numbers below IF & then on one line, next
line(s) other statement(s) then end if on the next line

---
Code from my UserForm
Private Sub cmdOk_Click()
CustName = UserForm1.CustName
If Len(CustName) < 1 Then 'line1
unload me 'line2
Exit Sub 'line3
end if 'line 4
Set Customers = Sheets("Customer List").Range("Customers")
If WorksheetFunction.CountIf(Customers, CustName) > 0 Then 'line1
unload me 'line2
exit sub 'line3
end if 'line4
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

 

[Kev]

Smiling again....thank you so much....works perfect.
This means I can now move onto next component of
spreadsheet - no doubt I will reappear in another post.
A little "knock it up in a night" spreadsheet gets more
detailed and more complicated but it is fun and learning -
particularly thanks to forum responders like yourself.
My thanks go to all those that assist...
Kev.

 

[JulieD]

Hi Kev

glad its working now .. i'll look out for your future posts and offer
assistance where possible.

Cheers
JulieD