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]

Sorry,
Its in the list box

 

[Fluff]

In that case add the line in blue as shown

         Case 2
            [COLOR=#0000ff]NameForDateEntryBox.Clear[/COLOR]
            NameForDateEntryBox.AddItem .Range("L1").Value
            .Range("L1").Clear
         Case Else

 

[ipbr21054]

OK
We are getting there.

With that code the following happens.

I am able to click each name so the list gets smaller.
I select the last name & press the transfer button.
I then see the updated message.
I then also see the message NO NAMES LEFT IN postage LIST
BUT
The last name is still there even though i see a date on the worksheet next to his name & also seen the updated message

 

[Fluff]

In that case simply add that line in blue to the 1st Case statement as well as the 2nd

 

[ipbr21054]

Perfect all sorted.

Thanks for your time & have a nice weekend.

 

[Fluff]

You're welcome & thanks for the feedback

 

[ipbr21054]

Morning,
This morning i opened the worksheet in question & i see the msgbox Open Postage User Form
Now assuming i dont know at this point if there are any names in the listbox i then select Yes.

I then see the msgbox Postage List Is Now Empty Of Customers names.
So i then click on OK.

I now see a run time error 91, Object variable or with block variable not set.
I now click on debug & see the following shown in yellow.

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 Then
  [COLOR=#ff0000]PostageTransferSheet.Show[/COLOR]
Else
  Exit Sub
End If
End Sub

I have it in my head by dont know how to put it down on paper but basically can we edit this so If answer = vbYes & L1 has a value Then PostageTransferSheet.Show

Many Thanks

 

[Fluff]

Try

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

 

[ipbr21054]

Hi,
This has got me scratching my head now.

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


There is no names in the list
I open the worksheet & i see the message Open Postage user form.
If i select NO the message closes as it should.
However if i select YES the message also closes BUT i then dont see the message POSTAGE LIST IS NOW EMPTY OF CUSTOMERS NAMES

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
MsgBox "POSTAGE LIST IS NOW EMPTY OF CUSTOMERS NAMES", vbExclamation, "POSTAGE LIST NO NAMES MESSAGE"
Else
  Exit Sub
End If
End Sub

 

[Fluff]

You don't get the userform, because you have removed the code that opened it.
Also you asked for the userform to open if L1 is NOT empty, which is the code I gave you. You are now trying to use that code to show a message box saying there are no names, when the code is ensuring that there IS at least one name.