When Closing the Workbook which is Opened : Subscript out of range Error ALso to clear contents and format of range

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
604
Hi

I have Project.xlsm file when userform of "Project.xlsm" initialised it opens one xlsx file. "StarServices.xlsx"
there is also worksheet name "Dir-File-Names" form where the "StarServices.Xlsx" is opened
Having coded in UF "Project.xlsm" Queryclose to close the xlsx
Subscript out of Range Erro i get. How can i prevent this error. Have gone thru many threads. but still unsuccessful
Also i want to clear isome of its range with clearContents and ClearFormats when closing with queryclose

Any possibilty ?

coding in Project.xlsm
Code:
Private Sub UserForm_Initialize()
Dim fpath As String ',
Dim picpath As String, pic As Picture
With ThisWorkbook.Sheets("Dir-File-Names")
      fpath = .Range("A3").value & ":\" & .Range("B3").value & "\" & .Range("C3").value & "\" & "StarServices.xlsx"
      Workbooks.Open Filename:=fpath
 End With
End Sub

Project.xlsm (userformQueryclose)
Code:
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
 Dim fpath As String
 Dim wBk As Workbook
   
  With ThisWorkbook.Sheets("Dir-File-Names")
      fpath = .Range("A3").value & ":\" & .Range("B3").value & "\" & .Range("C3").value & "\" & "StarServices.xlsx"
 End With
   
On Error GoTo 0
[COLOR=#ff0000][B]'''Error Observed Here
[/B][/COLOR] Set wBk = Workbooks(fpath)
  
If Application.ActiveWorkbook.Name = wBk.Name Then
   wBk.Close False
End If
End Sub
Thanks NimishK
 
Last edited:

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,178
Office Version
  1. 2019
Platform
  1. Windows
Hi,
try this update to your code & see if helps

Code:
Dim wBk As Workbook
Private Sub UserForm_Initialize()
    Dim fpath As String
    
    On Error GoTo exitevent
    With ThisWorkbook.Sheets("Dir-File-Names")
          fpath = .Range("A3").Value & ":\" & .Range("B3").Value & "\" & .Range("C3").Value & "\" & "StarServices.xlsx"
    End With
    
    If Dir(fpath, vbDirectory) <> vbNullString Then
      Set wBk = Workbooks.Open(FileName:=fpath)
    Else
        Err.Raise 600, , fpath & Chr(10) & "File Or Path Not Found"
    End If
    
exitevent:
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)


    If Not wBk Is Nothing Then wBk.Close False


End Sub

Dave
 

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
604
Dave
Yes Indeed Helped to get rid of errors. Thanks
But to implement .clearContents and .clearFormats not yet successful

Do i need to Activate the worksheet, if Yes then correct syntax because i know i am missing something
Code:
Private Sub UserForm_Initialize()
Coding............
'Option 1
Workbooks(fpath).Worksheets("Sheet7).Activate
or 
'Option 2
Worksheets("Sheet7").Activate   '''''of "StarServices.Xlsx"
The above syntax somewhere gives the Error "Subscript out of Range" Also Autmation Error
Is the Above syntax correct place in UF_Intiliaze to activate the sheet or do i place in CommandButton("Click")
By the way userform is modeless too
Also Sheet1 of Project.Xlsm is Active too

The reason to activate the sheet is to clear the contents and its formats when closing the userform

Code:
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Dim fpath As String
'With ThisWorkbook.Sheets("Dir-File-Names")
'      fpath = .Range("A3").value & ":\" & .Range("B3").value & "\" & .Range("C3").value & "\" & "Proforma-Invoice.xlsx"
'      Workbooks.Open Filename:=fpath
'      'Workbooks.Open Filename:="C:\Contract-Revenue\Proforma\Proforma-Invoice.xlsx"
' End With


Worksheets("Sheet7").Range("A1:j122").ClearContents
Worksheets("Sheet7").Range("A1:j122").ClearFormats


   If Not wBk Is Nothing Then wBk.Close False
End Sub
 
Last edited:

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,178
Office Version
  1. 2019
Platform
  1. Windows
try following

Code:
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)


    If Not wBk Is Nothing Then
    With wBk
        With .Worksheets("Sheet7").Range("A1:j122")
               .ClearContents
              .ClearFormats
        End With
        .Close False
    End With
    End If


End Sub

Dave
 

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
604

ADVERTISEMENT

Thanks Dave

Led to some confussion and then i changed from Uf_ initiliaze and placed the same code in command button to open StarServices.xlsx
Works fine to open the file
Code:
Private Sub CommandButton_Click()
Dim fpath As String ',
With ThisWorkbook.Sheets("Dir-File-Names")
      fpath = .Range("A3").value & ":\" & .Range("B3").value & "\" & .Range("C3").value & "\" & "StarServices.xlsx"
      Workbooks.Open Filename:=fpath
 End With
End Sub
Whats happening now is when toggling between uF of Project xlsm. there is worksheet Active of Project xlsm behind the UF

StarServices.xlsx : Sheet7 is also opened
when Uf_QueryClose triggered ie by clicking on X TRHCorner of UF Project.Xslm
Contents are not cleared of Sheet7 of StarServices.xlsx Is it because Workbook "StarServices.xlsx" is opened
Having observed above. How can i clearContents and ClearFormats

Thanks NimishK
 
Last edited:

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,178
Office Version
  1. 2019
Platform
  1. Windows
Hi,
You have not explained why taken a different approach but by changing the code in way you have you are no longer referencing object variable wBk which is Nothing when UserForm is closed hence the ranges not being cleared.

If you now want to open the workbook via a commandbutton elsewhere in your project but also want your userform to reference the opened workbook, you need to make the scope of your variable available to both procedures.

If take a look at code I updated in your userform, you will note the use of the SET statement which is required to assign an object variable to the workbook object.
You will also note I placed the object variable to the TOP of your forms code page, by doing this, it made it available to all procedures in the forms code page.

To share the variable between UserForm & elsewhere in your project you can as one method, move it to a standard module & declare it as Public.

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,114,193
Messages
5,546,479
Members
410,742
Latest member
WalterSil
Top