Upon opening worksheet check value is present in userform

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,620
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
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,620
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,077
You would need it like this:
If answer = vbYes And PostageTransferSheet.NameForDateEntryBox.Text = "" Then

You must provide the UserForm Name also
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,620
Form name is PostageTransferSheet

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

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,390
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,077
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,697
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:

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,620
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
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
2,909
.
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.
 

Forum statistics

Threads
1,078,466
Messages
5,340,484
Members
399,378
Latest member
voodoo1

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top