Page 1 of 4 123 ... LastLast
Results 1 to 10 of 33

Thread: Upon opening worksheet check value is present in userform

  1. #1
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,586
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Upon opening worksheet check value is present in userform

    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 NameForDateEntryBox.Text = "" Then
    Exit Sub
      PostageTransferSheet.Show
    Else
      Exit Sub
    End If
    End Sub
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,887
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Upon opening worksheet check value is present in userform

    What is:
    NameForDateEntryBox

    Is this a Activex TextBox on the active sheet?

    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  3. #3
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,586
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Upon opening worksheet check value is present in userform

    It is the name of my combobox on the userform.

    This is where the names are shown after being sorted in column L
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  4. #4
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,887
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Upon opening worksheet check value is present in userform

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

    You must provide the UserForm Name also
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  5. #5
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,586
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Upon opening worksheet check value is present in userform

    Form name is PostageTransferSheet

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

  6. #6
    Board Regular
    Join Date
    Sep 2004
    Posts
    1,389
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Upon opening worksheet check value is present in 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.
    Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Politicians combine theory and practice: nothing works and they donít know why.

  7. #7
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,887
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Upon opening worksheet check value is present in userform

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


    Quote Originally Posted by jolivanes View Post
    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.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  8. #8
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,648
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Upon opening worksheet check value is present in userform

    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 by mikerickson; Oct 11th, 2019 at 04:23 PM.

  9. #9
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,586
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Upon opening worksheet check value is present in userform

    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
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  10. #10
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    2,900
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Upon opening worksheet check value is present in userform

    .
    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •