1

Topic: excel.workbook is nothing = abuse also howl

Hello, respected
The form unit:

Option Explicit
Public wbA As Workbook
Private Sub CommandButton2_Click ()
If Me. TextBox1 = "" Then
MsgBox "Select a file!"
Else
If IsBookOpen (Me. TextBox1.Value) Then
MsgBox "the File is already opened!"
Exit Sub
Else
MsgBox "the specified file" Will be opened
Set wbA = Excel. Workbooks. Open (Me. TextBox1.Value)
End If
End If
End Sub
Private Sub CommandButton3_Click ()
If wbA Is Nothing Then
MsgBox "the Book is already closed!"
Else
If MsgBox ("the Book will be closed!", vbOKCancel) = 2 Then
Exit Sub
Else
If MsgBox ("to Save changes in" + wbA.Name + "?", vbOKCancel) = 2 Then
wbA.Close (False)
Else
wbA.Save
wbA.Close (False)
End If
End If
End If
End Sub

The separate unit:

Sub tmr1 ()
If userform1.wbA Is Nothing Then
userform1.Label1.ForeColor = &HFF&
userform1.Label2.ForeColor = &HFF&
userform1.Label1.Caption = "O" ' a character font - a dagger
userform1.Label2.Caption = "the File is not opened"
Else
userform1.Label1.ForeColor = &H8000&
userform1.Label2.ForeColor = &H8000&
userform1.Label1.Caption = "P" ' a character font - a tick
userform1.Label2.Caption = "the File is opened"
End If
End Sub

Function IsBookOpen:

Function IsBookOpen (wbName As String) As Boolean
Dim wbBook As Workbook: On Error Resume Next
Dim intPos %, filename As String
intPos = InStrRev (wbName, "\")
filename = Right (wbName, Len (wbName) - intPos)
wbName = filename
Set wbBook = Workbooks (wbName)
IsBookOpen = Not wbBook Is Nothing
End Function

The Question #1: at the first performance of procedure CommandButton3 (file closing) all happens according to plan but if to push the button once again at me the error, because falls out

userform1.wbA Is Nothing

Turns to something silly, not estimated, not calculated and as I layman to realize as it I can not win
Yes, it is possible to block the button that not  was, but I want to pick up this disgrace on the timer
The Question #2: how correctly to make check Me. TextBox1 that there really way to a file, instead of a word from 2 letters, for example
In advance thanks not indifferent!
____
vk.com/taenfox

2

Re: excel.workbook is nothing = abuse also howl

Did not understand, you want to close the book in which the code is fulfilled?

3

Re: excel.workbook is nothing = abuse also howl

Shocker. Pro, no, these actions are directed on other book

4

Re: excel.workbook is nothing = abuse also howl

MokeevP , closing of the book also should transform a variable storing the link to this book, into something as there one companion told, "silly, not estimated, not calculated". Therefore all conscious citizens after someObject. Close is mandatory do Set someObject = Nothing .

5

Re: excel.workbook is nothing = abuse also howl

Akina, true speak, thanks!
[spoiler] and I that think why the line someObj = nothing does not work for me [/spoiler]
And on the second question can help?

6

Re: excel.workbook is nothing = abuse also howl

MokeevP wrote:

Akina, true speak, thanks!
And on the second question can help?

if dir () = "" Then
MsgBox "the File is not present!"
Endif

7

Re: excel.workbook is nothing = abuse also howl

HandKot, thanks! Now I will fasten