Results 1 to 5 of 5

Thread: VBA To Close Workbook Based On Cell Value Change And Open New Workbook

  1. #1
    Member
    Join Date
    Apr 2014
    Posts
    45
    Rep Power
    0

    VBA To Close Workbook Based On Cell Value Change And Open New Workbook

    I use validation dropdown list to open some subworkbooks from this Main workbook “Main.xls”, i only need one subworkbook opened, if user opening a new subworkbook, the one that is already opened should be closed, what is the codes for this event? thanks for any help


    Code:
    Sub Open_Doc()
    
         Application.ScreenUpdating = False
    
         Workbooks.Open Filename _
            :=Sheets("Sheet1").Range("B2").Value & Sheets("Sheet1").Range("C2").Value
    
         Workbooks("Main.xls").Activate
        
         Application.ScreenUpdating = True
    
    End Sub
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C2")) Is Nothing Then
        Select Case Range("C2")
            Case "Doc_01.xls": Open_Doc
            Case "Doc_02.xls": Open_Doc
            Case "Doc_03.xls": Open_Doc
            Case "Doc_04.xls": Open_Doc
            Case "Doc_05.xls": Open_Doc
        End Select
    End If
    End Sub
    Last edited by Excel Fox; 04-10-2014 at 08:07 PM. Reason: Corrected Code Tags

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Try this

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        If Target.Address(0, 0) = "C2" Then
            Select Case Range("C2")
                Case "Doc_01.xls", "Doc_02.xls", "Doc_03.xls", "Doc_04.xls", "Doc_05.xls"
                    Open_Doc
                Case Else
                    'Do something / nothing
            End Select
        End If
        
    End Sub
    
    
    Sub Open_Doc()
    
        Dim var As Variant
        Dim wbk As Workbook
        
        Application.ScreenUpdating = False
        For Each var In Array("Doc_01.xls", "Doc_02.xls", "Doc_03.xls", "Doc_04.xls", "Doc_05.xls")
            On Error Resume Next
            Set wbk = Workbooks(CStr(var))
            Err.Clear: On Error GoTo 0: On Error GoTo -1
            If Not wbk Is Nothing Then
                wbk.Close True 'Use True/False to save changes or not
                Set wbk = Nothing
            End If
        Next var
        Workbooks.Open Sheets("Sheet1").Range("B2").Value & Sheets("Sheet1").Range("C2").Value
        Workbooks("Main.xls").Activate
        Application.ScreenUpdating = True
    
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Member
    Join Date
    Apr 2014
    Posts
    45
    Rep Power
    0
    possible to change this line to a range where I list all the WB names in, Range(“E1:E150”)

    Code:
       For Each var In Array("Doc_01.xls", "Doc_02.xls", "Doc_03.xls", "Doc_04.xls", "Doc_05.xls")
    and
    Sheet1("B2") = C:\My Docs\
    Sheet1("C2") = DataWB.xls

    how to change the


    Code:
    Case "Doc_01.xls", "Doc_02.xls", "Doc_03.xls", "Doc_04.xls", "Doc_05.xls"
    into something simple like, as i may have 100+ data WB,

    Code:
    Case "Sheets("Sheet1").Range("C2").Value": Open Doc
    Last edited by mrprofit; 04-10-2014 at 08:46 PM.

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Try this

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        If Target.Address(0, 0) = "C2" Then
            Select Case InStr(1, "|" & Join(Application.Transpose(Range("E1:E150").Value2), "|") & "|", "|" & Range("C2").Value2 & "|") > 0
                Case True
                    Open_Doc
                Case Else
                    'Do something / nothing
            End Select
        End If
        
    End Sub
    
    
    Sub Open_Doc()
    
        Dim var As Variant
        Dim wbk As Workbook
        Dim varWbkList As Variant
        
        varWbkList = Application.Transpose(Range("E1:E150").Value2)
        Application.ScreenUpdating = False
        For Each var In varWbkList
            On Error Resume Next
            Set wbk = Workbooks(CStr(var))
            Err.Clear: On Error GoTo 0: On Error GoTo -1
            If Not wbk Is Nothing Then
                wbk.Close True 'Use True/False to save changes or not
                Set wbk = Nothing
            End If
        Next var
        Workbooks.Open Sheets("Sheet1").Range("B2").Value & Sheets("Sheet1").Range("C2").Value
        Workbooks("Main.xls").Activate
        Application.ScreenUpdating = True
    
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Member
    Join Date
    Apr 2014
    Posts
    45
    Rep Power
    0
    Amazing, thank you so much

Similar Threads

  1. Replies: 6
    Last Post: 09-07-2013, 03:40 PM
  2. Replies: 6
    Last Post: 08-25-2013, 12:35 PM
  3. VBA Code to Open Workbook and copy data
    By Howardc in forum Excel Help
    Replies: 16
    Last Post: 08-15-2012, 06:58 PM
  4. Replies: 2
    Last Post: 12-12-2011, 01:51 PM
  5. Timer to close excel workbook
    By leopaulc in forum Excel Help
    Replies: 5
    Last Post: 10-24-2011, 12:31 PM

Posting Permissions

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