Run time error 1004 advice please

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,942
Office Version
  1. 2007
Platform
  1. Windows
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.

Code:
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,942
Office Version
  1. 2007
Platform
  1. Windows
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,898
Office Version
  1. 365
Platform
  1. Windows
Did you use the code in post#27 exactly as-is, or did you change it?
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,942
Office Version
  1. 2007
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,898
Office Version
  1. 365
Platform
  1. Windows
Can you please answer my question.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,942
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Post 27 was copied / pasted without alteration
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,898
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,942
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,898
Office Version
  1. 365
Platform
  1. Windows
You've now lost me completely. :confused:
If the userform code clears col L, why did you ask for code that checks col L for values before opening the userform?
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,942
Office Version
  1. 2007
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,898
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,190
Messages
5,640,742
Members
417,165
Latest member
Hilders1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top