Upon opening worksheet check value is present in userform

[ipbr21054]

Afternoon,

When i open my worksheet i see a msgbox asking if the userform should be opened.
Clicking on Yes should only open the form if NameForDateEntryBox.Text has a value in it.

Below is my code attemp but returns variable not defined thus i dont see this msgbox question

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


Dim answer As Integer
 
answer = MsgBox("Open Postage User Form ?", vbYesNo + vbQuestion, "POSTAGE USER FORM MESSAGE")
If answer = vbYes And [COLOR=#ff0000]NameForDateEntryBox[/COLOR].Text = "" Then
Exit Sub
  PostageTransferSheet.Show
Else
  Exit Sub
End If
End Sub

 

[ipbr21054]

Hi,

I already have a code using explicit.

Here it is below.

How / what should i now do,please advise thanks.

Option ExplicitPrivate Sub PostageCustomerDuplicateSearch_Click()


    Dim Cell As Variant
    Dim source As Range
    Dim dups As Long


    Set source = Range("B8:B5000")


    For Each Cell In source
        If Application.WorksheetFunction.CountIf(source, Cell) > 1 Then
            Cell.Interior.Color = RGB(0, 255, 0)
            dups = dups + 1
        End If
    Next Cell
    
    If dups > 0 Then
        MsgBox " DUPLICATES WERE FOUND AND HAVE BEEN HIGHLIGHTED ", vbExclamation, "POSTAGE DUPLICATE CHECKER"
    Else
        MsgBox " NO DUPLICATES WERE FOUND ", vbExclamation, "POSTAGE DUPLICATE CHECKER"
    End If


End Sub

 

[Logit]

.
This :

[COLOR=#333333]Option ExplicitPrivate Sub PostageCustomerDuplicateSearch_Click()[/COLOR]

Should be :

[COLOR=#333333]Option Explicit

Private Sub PostageCustomerDuplicateSearch_Click()

'rest of macro code.
[/COLOR]

 

[ipbr21054]

Hi,
It is but it just showed up as that due to it being pasted into the post

 

[Logit]

ok

 

[Logit]

.
Try without the OPTION EXPLICIT.

The macro works as presented .. here.

 

[ipbr21054]

I’ve ran out of time now and away working for one week.

I was hoping to get this fixed before I went it that was free case.

I will check upon my return

I don’t have excel on my laptop but so you know if something that will allow me to continue with this whilst away ?

 

[mikerickson]

Your workbook has just been opened.
PostageTransferSheet is unloaded.
How would there be anything (except the default value) in any of the userform's controls?

 

[ipbr21054]

Morning,
I am now back & wishing to continue with this post.
Below is the code in use.

My aim is to only show userform if there are more than 1 name in the NameForDateEntryBox field

So i open my worksheet.
I see the msgbox asking if i would like to open the userform.
Selecting No would close the msgbox
Selecting Yes would then ONLY open the userform if there are more than 1 name in it.
If there is 1 name then show msgbox "No names Left"
Clicking Ok would then close msgbox

I see no other way to fix this so this will be the last attempt.

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


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

 

[mikerickson]

The logic of your desired action is flawed.

So i open my worksheet.
I see the msgbox asking if i would like to open the userform.
Selecting No would close the msgbox
Selecting Yes would then ONLY open the userform if there are more than 1 name in it.

At that point, the only entry in the userform would be the default entries. "if there are more than 1 name" would never change.

 

[ipbr21054]

OK thanks then i give up with this one.