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]

FYI

If i remove the part EXIT SUB then try i see the message about list being empty and when i click OK the form is then shown

 

[Fluff]

Did you use the code in post#27 exactly as-is, or did you change it?

 

[ipbr21054]

Ive just had a thought.

The names in the userform listbox are ordered A-Z
Am i correct in thinking the only way to get this sorted A-Z is to sort it before its put in the list box.

I mean collect all the names from the worksheet sort them in column L then enter the names in the list box.

If so cell L1 will be empty.

Make sense ?

Also the names that are sorted & put in order are any name which does not have a date next to them.
So if there is a date then that name isnt used.

So the dates are placed in column G


I understand its hard for you to understand and as i have the worksheet in front of me but i think that is where the issue lies

 

[Fluff]

Can you please answer my question.

 

[ipbr21054]

Post 27 was copied / pasted without alteration

 

[Fluff]

In that case if you select yes from the 1st message box, it will check if L1 is blank. If it is you will get the 2nd msgbox, otherwise the userform will open.

If you get the 2nd msgbox then L1 on the active sheet is blank.

 

[ipbr21054]

I agree with the above statement.
However there is a name in the userform listbox.

I mentioned aboiut the sorting of names A-Z

I think the names in column A that do not have a date against them from column G are then copied to column L
Sorted A-Z in column L
Column L is then cleared
These names A-Z are now inserted into the userform listbox

So when the code checks column L it will be empty because it was cleared

 

[Fluff]

You've now lost me completely.
If the userform code clears col L, why did you ask for code that checks col L for values before opening the userform?

 

[ipbr21054]

Probably becuase i read post 1 incorrect & 2 as im an idiot.


What bit did i confuse you with or was all of it ?

The list box is sorted A-Z
The names are taken from the worksheet in column A

If you require names sorted A-Z in a list box you need to do it before entered into the listbox correct ?

So the names are gathered,sorted in column L in the order of A-Z THEN entered into the list box.

This sound correct

 

[Fluff]

Forget about sorting data etc as it has nothing to do with this thread.

In you op you had problems with populating the listbox if there was only 1 name in the list, which has now been sorted.
You then said you were having problems with the code that activated the userform and asked

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

But if the userform code clears col L, why did you want to check if there is a value there?
I would suggest you go back to the original code you had in the worksheet_activate event.