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

[NimishK]

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

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)

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

 

[dmt32]

Hi,
try this update to your code & see if helps

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]

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

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

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

 

[dmt32]

try following

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]

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

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

 

[dmt32]

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

 

[NimishK]

Thanks Dave I understood and sorry for late reply