+ Reply to Thread
Results 1 to 7 of 7

Open a Excel file selecting a option in Combobox

Hybrid View

bluepomme Open a Excel file selecting a... 06-13-2019, 09:23 PM
bakerman2 Re: Open a Excel file... 06-13-2019, 10:48 PM
bluepomme Re: Open a Excel file... 06-14-2019, 07:15 PM
bakerman2 Re: Open a Excel file... 06-14-2019, 07:28 PM
bluepomme Re: Open a Excel file... 06-14-2019, 11:34 PM
bakerman2 Re: Open a Excel file... 06-15-2019, 01:31 AM
bluepomme Re: Open a Excel file... 06-18-2019, 08:10 PM
  1. #1
    Registered User
    Join Date
    10-04-2018
    Location
    Cancun, Mexico
    MS-Off Ver
    2013 & 2016 32b
    Posts
    78

    Open a Excel file selecting a option in Combobox

    Hello

    I need help guiding me with a Userfom i have .
    I´d like to select a mouth in a combobox that opens a excel file after checking the file is not in use , what i have is this:
    Private Sub ComboBox1_Change()
    Select Case ComboBox1.Value
            Case "enero"
                Unload PLATINUM
                If IsFileOpen("I:\CONCIERGE\MarkuM M\BASE DE DATOS BITACORAS (NO TOCAR NO MOVER NI BORRAR)\BITACORA 2019 ENERO MARINA.xlsm") = True Then YAESTAABIERTO.Show
    If Not IsFileOpen("I:\CONCIERGE\MarkuM M\BASE DE DATOS BITACORAS (NO TOCAR NO MOVER NI BORRAR)\BITACORA 2019 ENERO MARINA.xlsm") Then
    Workbooks.Open "I:\CONCIERGE\MarkuM M\BASE DE DATOS BITACORAS (NO TOCAR NO MOVER NI BORRAR)\BITACORA 2019 ENERO MARINA.xlsm"
            Case "febrero"
                Unload PLATINUM
                If IsFileOpen("I:\CONCIERGE\MarkuM M\BASE DE DATOS BITACORAS (NO TOCAR NO MOVER NI BORRAR)\BITACORA 2019 FEBRERO MARINA.xlsm") = True Then YAESTAABIERTO.Show
    If Not IsFileOpen("I:\CONCIERGE\MarkuM M\BASE DE DATOS BITACORAS (NO TOCAR NO MOVER NI BORRAR)\BITACORA 2019 FEBRERO MARINA.xlsm") Then
    Workbooks.Open "I:\CONCIERGE\MarkuM M\BASE DE DATOS BITACORAS (NO TOCAR NO MOVER NI BORRAR)\BITACORA 2019 FEBRERO MARINA.xlsm"
                
            Case Else
        End Select
    End Sub
    but the code does nothing, it works only when i aply the code on a comand botton.
    is there a different case using it with a combobox ?
    thanks

  2. #2
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MSO Home and Business 2024
    Posts
    7,275

    Re: Open a Excel file selecting a option in Combobox

    Code can be simplified but I see no reason why it wouldn't work with the Change-event.

    The only thing I can think of is that you clicked on the name that was selected before. You need to select another value then the one being displayed in the CB.

    Private Sub ComboBox1_Change()
        Dim BookToOpen As String
        
        BookToOpen = "I:\CONCIERGE\MarkuM M\BASE DE DATOS BITACORAS (NO TOCAR NO MOVER NI BORRAR)\BITACORA 2019 " & Combobox1.Value & " MARINA.xlsm"
        Unload PLATINUM
        If IsFileOpen(BookToOpen) = True Then
            YAESTAABIERTO.Show
        Else
            Workbooks.Open BookToOpen
        End If
    End Sub
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  3. #3
    Registered User
    Join Date
    10-04-2018
    Location
    Cancun, Mexico
    MS-Off Ver
    2013 & 2016 32b
    Posts
    78

    Re: Open a Excel file selecting a option in Combobox

    thank you

    it works fine now ...also thank you to trim my code since it will be usefull when file directory changes to modify quickly

  4. #4
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MSO Home and Business 2024
    Posts
    7,275

    Re: Open a Excel file selecting a option in Combobox

    you're welcome and thanks for rep+.

  5. #5
    Registered User
    Join Date
    10-04-2018
    Location
    Cancun, Mexico
    MS-Off Ver
    2013 & 2016 32b
    Posts
    78

    Re: Open a Excel file selecting a option in Combobox

    sorry

    i just find out that when the file is alrady open instad to show form YAESTAABIERTO.Show and close the workbook
    excel still would opend it as a copy and does not show the YAESTAABIERTO.Show


  6. #6
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MSO Home and Business 2024
    Posts
    7,275

    Re: Open a Excel file selecting a option in Combobox

    Is PLATINUM the userform where the CB is in ?

    Try moving the Unload to the end of the code.

    What does your IsFileOpen code look like ?

  7. #7
    Registered User
    Join Date
    10-04-2018
    Location
    Cancun, Mexico
    MS-Off Ver
    2013 & 2016 32b
    Posts
    78

    Re: Open a Excel file selecting a option in Combobox

    it is working perfect moving the unload to the end of the code

    thank you again

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] I need VBA code to make each combobox option on a userform open an excel workbook ...
    By Jalica in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-15-2016, 02:57 PM
  2. Open With... Excel not an option for an Access file?
    By CWatsonJr in forum Excel General
    Replies: 4
    Last Post: 11-17-2015, 12:30 AM
  3. Replies: 1
    Last Post: 11-07-2014, 01:21 PM
  4. Replies: 0
    Last Post: 07-18-2012, 09:45 AM
  5. password to open Excel file option does not exist
    By SabrinaN in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-22-2006, 11:00 PM
  6. Selecting AutoFilter option based on ComboBox value
    By clmarquez in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-08-2005, 09:24 PM
  7. Get data from ext. file when i choose an option from combobox
    By hmoner in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-17-2005, 01:16 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1