Upon opening worksheet check value is present in userform

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,838
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,838
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,297
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,838
Form name is PostageTransferSheet

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

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,418
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,297
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,807
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,838
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,924
.
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,082,380
Messages
5,365,124
Members
400,824
Latest member
Themilkybarkid

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top