Run time error 1004 advice please

ipbr21054

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

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

ipbr21054

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
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
Code:
If answer = vbYes [COLOR=#0000ff]And Range("L1") <> "" [/COLOR]Then
 

ipbr21054

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Code:
[COLOR=#ff0000]PostageTransferSheet.Show[/COLOR]
 
Last edited:

ipbr21054

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

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
Rich (BB code):
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

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
It does for me, what sheet is that code in?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,475
Messages
5,636,553
Members
416,923
Latest member
jarri

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