Run time error 1004 advice please

[ipbr21054]

Hi.
On my useform i select a name from a list & then press the transfer button.
Now on my worksheet todays date has been entered in the cell which refers to the name i had selected on the userform.

Normally the list on the userform has many names so this is a first for me because now there are two names.
So whats happens is,
I select one of the names & press the transfer button.
On my worksheet the date is placed next to that name so far all is ok.

So now there is only one name left in the list
I select this name & press the transfer button & i see the updated message but then i get the run time error message because the list doesnt have any names to show.

Can we now at this stage show maybe a msgbox saying No Names In List etc as opposed to the run time error beeing shown.

I think the issue is related to the red text shown but not sure how to proceed with the fix.

Private Sub UserForm_Initialize()Dim cl As Range
Dim rng As Range
Dim lstrw As Long
Dim lastrow As Long
Dim Lastrowa As Long
Dim cntr As Integer
Load PostageTransferSheet
'==============================================================================================
Application.ScreenUpdating = False
lastrow = Sheets("POSTAGE").Cells(Rows.Count, "B").End(xlUp).Row
Sheets("POSTAGE").Cells(8, 2).Resize(lastrow - 7).Copy Sheets("POSTAGE").Cells(1, 12)
Lastrowa = Sheets("POSTAGE").Cells(Rows.Count, "L").End(xlUp).Row
Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Sort key1:=Cells(1, 12).Resize(Lastrowa), order1:=xlAscending, Header:=xlNo
CustomerSearchBox.List = Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Value
Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Clear
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
cntr = 1
With Sheets("POSTAGE")
    lstrw = .Range("B65536").End(xlUp).Row
    Set rng = .Range("B8:B" & lstrw)
        For Each cl In rng
            If cl.Offset(0, 5).Value = "" Then Sheets("POSTAGE").Range("L" & cntr).Value = cl.Value: cntr = cntr + 1
        Next
     .Range("L1:L" & cntr - 1).Sort key1:=.Range("L1"), order1:=xlAscending, Header:=xlNo
[COLOR=#ff0000]      NameForDateEntryBox.List = .Range("L1:L" & cntr - 1).Value[/COLOR]
     .Range("L1:L" & cntr - 1).Clear
     TextBox2.SetFocus
End With
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Application.ScreenUpdating = True


TextBox1.Value = Format(CDbl(Date), "dd/mm/yyyy")
TextBox7.Value = Format(CDbl(Date), "dd/mm/yyyy")
End Sub

 

[ipbr21054]

Hi,

You wrote,
Also you asked for the userform to open if L1 is NOT empty

My info above said that 1 name was in the list BUT the form didnt open

Or am i missing something here ?

 

[ipbr21054]

Also

If answer = vbYes And Range("L1") <> "" Then

I read the above as if i select Yes & L1 is empty then show message telling me no names in the list.
My post #19 said i selected yes but no message was shown telling me list was empty

 

[Fluff]

You said

can we edit this so If answer = vbYes & L1 has a value Then PostageTransferSheet.Show

So the code I suggested does exactly what you asked for. all you had to do was add the part in blue to the existing code

If answer = vbYes [COLOR=#0000ff]And Range("L1") <> "" [/COLOR]Then

 

[ipbr21054]

I agree with that & what you have advised

BUT
What i am saying in post #19 is

There is 1 name in the list.
I open the worksheet & i see the message Open Postage user form.
If i select NO the message closes & all is ok.
However if i select YES the message also closes.
It should open the form as there is 1 name in the list

Its supposed to OPEN but it doesnt

 

[Fluff]

I fully understand that, which is why I said.

You don't get the userform, because you have removed the code that opened it.

That is to say you have removed this line of code

[COLOR=#ff0000]PostageTransferSheet.Show[/COLOR]

 

[ipbr21054]

Hmmm
Im now trying to work it out as we have a yes / no option but do we have 3 possibilites of answers ?

1 = No dont open form
2 = Yes open form but only if L1 has a value
3 = Yes but if L1 is empty then show message advising this

Does that seem correct.

This is what i have in place at present

Private Sub Worksheet_Activate()Application.GoTo Sheets("POSTAGE").Range("A" & Rows.Count).End(xlUp).Offset(1, 0), True
ActiveWindow.SmallScroll UP:=16


Dim answer As Integer
 
answer = MsgBox("Open Postage User Form ?", vbYesNo + vbExclamation, "POSTAGE USER FORM MESSAGE")
If answer = vbYes And Range("L1") <> "" Then
PostageTransferSheet.Show
Else
Exit Sub
End If
End Sub

Maybe i asked for the wrong thing ?
Maybe it should be If Then Else ?

Remember im a bit thick

 

[Fluff]

How about

Private Sub Worksheet_Activate()
Application.Goto Sheets("POSTAGE").Range("A" & Rows.Count).End(xlUp).Offset(1, 0), True
ActiveWindow.SmallScroll UP:=16


 
If MsgBox("Open Postage User Form ?", vbYesNo + vbExclamation, "POSTAGE USER FORM MESSAGE") = vbNo Then Exit Sub
If Range("L1") = "" Then
   MsgBox "POSTAGE LIST IS NOW EMPTY OF CUSTOMERS NAMES", vbExclamation, "POSTAGE LIST NO NAMES MESSAGE"
   Exit Sub
End If
PostageTransferSheet.Show
End Sub

 

[ipbr21054]

With that code selecting Yes does not open the form even when there is a name in the list

It actually shows me the message telling me no names are in the list which is incorrect

 

[Fluff]

It does for me, what sheet is that code in?

 

[ipbr21054]

Its in the Postage worksheet