Results 1 to 16 of 16

Public variable and Runtime error 91: Object Variable or With block variable not set

Threaded View

Frank Nunez Public variable and Runtime... 10-29-2020, 03:11 PM
6StringJazzer Re: Public variable and... 10-29-2020, 04:10 PM
Frank Nunez Re: Public variable and... 10-29-2020, 05:25 PM
6StringJazzer Re: Public variable and... 10-29-2020, 06:23 PM
Frank Nunez Re: Public variable and... 10-29-2020, 06:40 PM
6StringJazzer Re: Public variable and... 10-29-2020, 09:08 PM
hrlngrv Re: Public variable and... 10-29-2020, 07:02 PM
Frank Nunez Re: Public variable and... 10-29-2020, 10:11 PM
6StringJazzer Re: Public variable and... 10-30-2020, 10:57 AM
romperstomper Re: Public variable and... 10-30-2020, 11:13 AM
romperstomper Re: Public variable and... 10-30-2020, 04:27 AM
Frank Nunez Re: Public variable and... 10-30-2020, 10:41 AM
Frank Nunez Re: Public variable and... 10-30-2020, 12:43 PM
Frank Nunez Re: Public variable and... 10-31-2020, 12:05 AM
6StringJazzer Re: Public variable and... 10-31-2020, 12:04 PM
Frank Nunez Re: Public variable and... 10-31-2020, 04:02 PM
  1. #1
    Registered User
    Join Date
    08-08-2020
    Location
    Boston
    MS-Off Ver
    2019
    Posts
    33

    Public variable and Runtime error 91: Object Variable or With block variable not set

    Hi all, I have a userform with a textbox that I load from a standard module that I use to edit cell comments more easily than Excel's comment boxes. Everything works (ie. changing cells and the textbox updating to the new cell's comment, automatic storing of the textbox data to the previous cell's comment. What doesn't work is when a cell has no comment, and I add one to the textbox and then click on a different cell, I get thrown a Object variable or with block not set on the line bolded below.

    Also, in a standard module elsewhere, I set the public variable PrevCellRange to the active cell upon load of the userform as follows:
           PrevCellRange = Application.ActiveCell.Address
    Then in my worksheet module, I have the following main code, with public variables set atop the module:


    Public PrevCellRange As String
    Public NewCell As String
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Application.ScreenUpdating = False
    
        PrevCellRange = NewCell
        NewCell = ActiveCell.Address
    
        If myUserForm.myTextBox <> vbNullString And Range("userFormIsLoaded") = 1 Then 
            If Range(PrevCellRange).Comment.Text <> vbNullString Then
                Range(PrevCellRange).Comment.Delete    
            End If
            Range(PrevCellRange).AddComment.Text Text:=myUserForm.myTextBox.Text  
        End If
    
    'Refresh Text Box with Cell Change
        If Application.ActiveCell.Comment Is Nothing Then
            myUserForm.myTextBox.Text = ""
        Else
            myUserForm.myTextBox.Text = Application.ActiveCell.Comment.Text
        End If
    Thanks all for your help again, this is causing me to pull my hair out. Frank
    Last edited by Frank Nunez; 10-29-2020 at 06:23 PM. Reason: I put wrong code in

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Runtime error 91: Object variable or With block variable not set
    By JohnTaitor in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-09-2019, 06:29 AM
  2. Runtime Error 91 Object variable or with block variable not set
    By pooky in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-17-2019, 08:31 AM
  3. [SOLVED] VBA Runtime Error 91: Object variable or With block variable not set.
    By Ravana in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-25-2018, 09:15 AM
  4. [SOLVED] Runtime Error 91: Object variable or With block variable not set
    By Andrew.Trevayne in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-21-2018, 09:12 AM
  5. Runtime Error 91 - object variable or with block variable not set
    By 0celj in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-23-2013, 09:40 PM
  6. runtime error 91 object variable or With block variable not set
    By tullemann in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-14-2011, 01:27 PM
  7. Replies: 8
    Last Post: 02-17-2009, 02:24 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1