VBA run-time error ‘91’: Object variable or With block variable not set

Question

I'm brand new here and although I'm super handy with Excel I have minimal knowledge when it comes to VBA. I've had an Excel program that we use at work dropped in my lap because it's started throwing multiple errors and I'm the "best" person for the job :(

I've searched this most excellent forum and a couple others and have been able to solve most of my problems, but now a new error is popping up and I cannot figure out why.

Run-time error ‘91’: Object variable or With block variable not set

It pops up when I start the program.

The highlighted area in debugger is:

If ActiveWorkbook.Name = wbkTT Then

I'm posting the section of code below

Thanks a lot for any and all help!

' TTV2.xlam version 5/15/12 14:00

Const wbkTT As String = "Time Tracker ver2.xlsm"
Const RibbonxAddin As String = "TTV2.xlam"


Option Explicit
Private Sub Workbook_Open()

Dim strName As String
Dim strPath As String
Dim strNewShow As String
Dim strDate As String
Dim strTime As String
Dim strDepartment As String
Dim strOS As String
Dim strVersion As String
Dim dtFormat As String
Dim myAddIn As AddIn
Dim wbkDateOrder As Integer
Dim timedropdown As Boolean

strVersion = Application.Version
strOS = Application.OperatingSystem
dtFormat = DateFormat
newTTwbk = wbkTT

If strVersion < 14 Then
    MsgBox ("You are running an outdated version of Excel." & Chr(10) & _
        "Time Tracker will only run on MS Excel 2011 for Mac")
    ActiveWorkbook.Close
Exit Sub
Else
    wbkDateOrder = Application.International(xlDateOrder)
    strTime = Format(time, "hhmm")
    strDate = Format(Date, "mmdd")
    If ActiveWorkbook.Name = wbkTT Then
        strName = Replace(Workbooks(wbkTT).Sheets("Show Info").Range("A2").Value, " ", "")
        ActiveWorkbook.Sheets("Preferences").Range("D2:D3").NumberFormat = dtFormat
        ActiveWorkbook.Sheets("Data").Range("B:B").NumberFormat = dtFormat
    End If '
AddMenu
    ActiveWorkbook.Sheets("Preferences").visible = True
    timedropdown = ActiveWorkbook.Sheets("Preferences").Range("E3")
    If timedropdown Then
        ActiveWorkbook.Sheets("Data").Range("G:L").Validation.Delete
        ActiveWorkbook.Sheets("Data").Range("G:L").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Time"
    Else
        ActiveWorkbook.Sheets("Data").Range("G:L").Validation.Delete
    End If
   If ActiveWorkbook.Sheets("Show Info").Range("A2").Value = "TTV2" Then
        If strOS Like "*Win*" Then
            strPath = ThisWorkbook.Path & "\"
        Else
            strPath = ThisWorkbook.Path & ":"
        End If
        strName = InputBox(Prompt:="Show Name please.", Title:="ENTER SHOW NAME", Default:="Show Name Here")
        strDepartment = InputBox(Prompt:="Enter your Department:", Title:="ENTER DEPARTMENT", Default:="Department Here")
        showcrewform
      If strName = "Show Name Here" Or strName = vbNullString Then
            Exit Sub
        Else
            ActiveWorkbook.Sheets("Show Info").Range("A2").Value = strName
            ActiveWorkbook.Sheets("Show Info").Range("B2").Value = strDepartment
            If Not strOS Like "*Mac*" Then
                strNewShow = strPath & strName & "_" & strDate & "_" & strTime & ".xlsm"
            Else
                strNewShow = strPath & strName & "_" & strDate & "_" & strTime & ".xlsm"
            End If
            ActiveWorkbook.SaveAs strNewShow, FileFormat:=xlOpenXMLWorkbookMacroEnabled
            MsgBox ("You are now working on the file: " & strNewShow)
            loadwbkName
            ResetDataSheet
            Exit Sub
        End If
    End If
    If strOS Like "*Mac*" Then
        AddMenu
    End If
    ResetDataSheet
End If
End Sub

Do you have an 'End If' statement to close the previous If Else statement? — B Hart
– B Hart, Commented Oct 24, 2014 at 15:38
Besides, if end if thing, you use option explicit but did not dim newTTwbk? — ZAT
– ZAT, Commented Oct 24, 2014 at 15:47
Hi B Hart, thanks for the response. Placing an 'End If' statement between the 'Exit Sub' and 'Else' causes an "Else without If error"...? — The Enthusiast
– The Enthusiast, Commented Oct 24, 2014 at 15:49
The 'missing' End If needs to go at the very bottom of the code you posted... — B Hart
– B Hart, Commented Oct 24, 2014 at 15:51

B Hart · Accepted Answer · 2014-10-24 17:24:26Z

1

Added some missing declarations and End If statement... I don't get any run-time error with the below. (EDIT: Added new code)

' TTV2.xlam version 5/15/12 14:00

Const wbkTT As String = "Time Tracker ver2.xlsm"
Const RibbonxAddin As String = "TTV2.xlam"

'ADDED DECLARATIONS
Dim dtFormat As String
Dim DateFormat
Dim newTTwbk

Option Explicit

Private Sub Workbook_Open()
Dim strName As String
Dim strPath As String
Dim strNewShow As String
Dim strDate As String
Dim strTime As String
Dim strDepartment As String
Dim strOS As String
Dim strVersion As String
Dim dtFormat As String
Dim myAddIn As AddIn
Dim wbkDateOrder As Integer
Dim timedropdown As Boolean

' ADDED DECLARATIONS
Dim WB As Workbook

'ADDED CODE
Set WB = ActiveWorkbook

strVersion = Application.Version
strOS = Application.OperatingSystem
dtFormat = DateFormat
newTTwbk = wbkTT

If strVersion < 14 Then
    MsgBox ("You are running an outdated version of Excel." & Chr(10) & _
        "Time Tracker will only run on MS Excel 2011 for Mac")
    WB.Close 'SAVE? (TRUE/FALSE)
    Exit Sub
Else
    wbkDateOrder = Application.International(xlDateOrder)
    strTime = Format(Time, "hhmm")
    strDate = Format(Date, "mmdd")
    If WB.Name = wbkTT Then
        strName = Replace(Workbooks(wbkTT).Sheets("Show Info").Range("A2").Value, " ", "")
        WB.Sheets("Preferences").Range("D2:D3").NumberFormat = dtFormat
        WB.Sheets("Data").Range("B:B").NumberFormat = dtFormat
    End If
    'AddMenu
    WB.Sheets("Preferences").Visible = True
    timedropdown = WB.Sheets("Preferences").Range("E3")
    If timedropdown Then
        WB.Sheets("Data").Range("G:L").Validation.Delete
        WB.Sheets("Data").Range("G:L").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Time"
    Else
        WB.Sheets("Data").Range("G:L").Validation.Delete
    End If
    If WB.Sheets("Show Info").Range("A2").Value = "TTV2" Then
        If strOS Like "*Win*" Then
            strPath = ThisWorkbook.Path & "\"
        Else
            strPath = ThisWorkbook.Path & ":"
        End If
        strName = InputBox(Prompt:="Show Name please.", Title:="ENTER SHOW NAME", Default:="Show Name Here")
        strDepartment = InputBox(Prompt:="Enter your Department:", Title:="ENTER DEPARTMENT", Default:="Department Here")
        'showcrewform
        If strName = "Show Name Here" Or strName = vbNullString Then
            Exit Sub
        Else
            WB.Sheets("Show Info").Range("A2").Value = strName
            WB.Sheets("Show Info").Range("B2").Value = strDepartment
            If Not strOS Like "*Mac*" Then
                strNewShow = strPath & strName & "_" & strDate & "_" & strTime & ".xlsm"
            Else
                strNewShow = strPath & strName & "_" & strDate & "_" & strTime & ".xlsm"
            End If
            WB.SaveAs strNewShow, FileFormat:=xlOpenXMLWorkbookMacroEnabled
            MsgBox ("You are now working on the file: " & strNewShow)
            'loadwbkName
            'ResetDataSheet
            Exit Sub
        End If
    End If
    If strOS Like "*Mac*" Then
        'AddMenu
    End If
    'ResetDataSheet
End If
End Sub

edited Oct 24, 2014 at 17:24

answered Oct 24, 2014 at 15:55

B Hart

1,10811 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

The Enthusiast Over a year ago

Thanks B Hart, it did already have "Dim dtFormat As String", was/is that good enough? And I made your changes but it's still throwing the 91 error... I did add the last 15 lines of code up in my original question. I would be so appreciative if you could take a look at it. Thanks!

The Enthusiast Over a year ago

Well that's kind of the thing, when I open the file I get the error and if I hit "end" it stops opening the program right there. If I hit "debug" it takes me to the ActiveWorkbook line. If I step through the code from there it runs right through to the end. without problems, activates my workbook and macros, and the program functions as it should. If I were the only one using it I would just live with it ;) I'm going to try your new declarations right now, thanks!

The Enthusiast Over a year ago

B Hart, thanks for all your hard work. I now get the error: If WB.Name = wbkTT The only difference now is I cannot step over the error to activate the program...

B Hart Over a year ago

After Set WB... Add MsgBox WB Is Nothing and also try MsgBox TypeName(WB) - What do you get? Its possible your version of Excel does not like the use of ActiveWorkbook.Name... You can also try MsgBox ActiveWorkbook.Name to see if it Returns a Value...

B Hart Over a year ago

You can also try: StrComp(WB.Name, wbkTT, 1) = 0 Instead of WB.Name = wbkTT... Both work fine for me. Try to also Google the Error Number and Message its giving you.

Collectives™ on Stack Overflow

VBA run-time error ‘91’: Object variable or With block variable not set

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related