Upon opening worksheet check value is present in userform

ipbr21054

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

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

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
What is:
NameForDateEntryBox

Is this a Activex TextBox on the active sheet?

 
Upvote 0
It is the name of my combobox on the userform.

This is where the names are shown after being sorted in column L
 
Upvote 0
You would need it like this:
If answer = vbYes And PostageTransferSheet.NameForDateEntryBox.Text = "" Then

You must provide the UserForm Name also
 
Upvote 0
Form name is PostageTransferSheet

I will check this code once back.
Thanks very much.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.


Code:
MsgBox Userforms.Count

Userform1.TextBox1.Text = "test"

' userform is now loaded

MsgBox Userforms.Count

UnLoad Userform1

MsgBox Userforms.Count
 
Last edited:
Upvote 0
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


Code:
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
 
Upvote 0
.
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

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
Back
Top