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

 

[My Aswer Is This]

What is:

NameForDateEntryBox

Is this a Activex TextBox on the active sheet?

​

 

[ipbr21054]

It is the name of my combobox on the userform.

This is where the names are shown after being sorted in column L

 

[My Aswer Is This]

You would need it like this:
If answer = vbYes And PostageTransferSheet.NameForDateEntryBox.Text = "" Then

You must provide the UserForm Name also

 

[ipbr21054]

Form name is PostageTransferSheet

I will check this code once back.
Thanks very much.

 

[jolivanes]

AFAIK, there will be nothing in a UserForm until initialization when it is opened.
Maybe you can run a check on that part of the initialization that populates the combobox.

 

[My Aswer Is This]

I test my scripts and some values can be obtained from a closed UserForm.

AFAIK, there will be nothing in a UserForm until initialization when it is opened.
Maybe you can run a check on that part of the initialization that populates the combobox.

 

[mikerickson]

Default values can be gotten from an unloaded userform.
But addressing any of the uf properties will automatically load the userform (and run the Initialize event, but not the Activate event), even if it doesn't show the userform.
For example, this code will load Userform1, but not show it.

MsgBox Userforms.Count

Userform1.TextBox1.Text = "test"

' userform is now loaded

MsgBox Userforms.Count

UnLoad Userform1

MsgBox Userforms.Count

 

[ipbr21054]

Hi,
Im getting a bit confused with this.
I have the code below, did i do it correct ?

I click on the worksheet.
I see the I see the msgbox Open Postage User Form.
If i click Yes or No i see the next msgbox ALL PARCELS HAVE NOW BEEN DELIVERED.
I click OK but see Object variable or with block variable not set.
I click debug and this is in yellow
If answer = vbYes And PostageTransferSheet.NameForDateEntryBox.Text = "" Then

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 PostageTransferSheet.NameForDateEntryBox.Text = "" Then
Exit Sub
Else
  PostageTransferSheet.Show
  Exit Sub
End If
End Sub

 

[Logit]

.

Option Explicit


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 PostageTransferSheet.NameForDateEntryBox.Text = "" Then


  PostageTransferSheet.Show
Else
  Exit Sub
End If
End Sub

Right click on the sheet POSTAGE tab. Select VIEW CODE. Paste the above macro in the rightside window.