+ Reply to Thread
Results 1 to 8 of 8

OnOpen event

  1. #1
    Registered User
    Join Date
    03-20-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    16

    OnOpen event

    Hi All

    I regularly send out .xls files to clients, and (for reasons you might guess, but which aren't relevant here) I need to have a button on Sheet1 of the excel file that, when clicked by the client, will save a copy of the current workbook to the same directory as the current workbook, but with the file extension amended from .xls to .abc . I would like the button to have the caption "Save this file as C:/My Documents/admin/June13/summary.abc" - but using the actual path and filename, which will vary.

    I'm pretty familiar with VBA coding in Access, and I've been googling how to do this, and I think I'm nearly there.

    So far I have:
    In the ThisWorkbook module
    Please Login or Register  to view this content.
    and in Module1:
    Please Login or Register  to view this content.
    (I've not shown the FindAndReplace function, which is also in Module1, and works fine)

    This all seems to work, but I don't understand why the Module1 code has to be in Module1 and not in Sheet1: it didn't work when I tried it there
    I also don't really get how the particular button (which will always be on Sheet1) is referenced. This needs to be right before I go any further, and I'm concerned that I'm not going about it the right way (if I am - great .

    In case it's relevant, the workbook is in fact created from an Access module (using a template), with Access VBA being used to populate some data and to create lots of dropdowns, validation rules, formatting, etc.

    Hope someone can help.
    Thanks in advance
    Les

  2. #2
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: OnOpen event

    Hi Les,

    I also don't really get how the particular button (which will always be on Sheet1) is referenced.
    It's referenced here:
    Please Login or Register  to view this content.
    where "Sheet1" is the codename (not the name you see) of the sheet containing the shape "Button1 ".


    but I don't understand why the Module1 code has to be in Module1 and not in Sheet1: it didn't work when I tried it there
    If you want assign a sub to the shape (button in this case) you have to reference the sheet while calling the sub, e.g.
    Please Login or Register  to view this content.
    You also could use the ActiveX Control button, this one has its event in the appropriate sheet only.

    Regards
    Please use [CODE]-TAGS
    When your problem is solved mark the thread SOLVED
    If an answer has helped you please click to give reputation
    Read the FORUM RULES

  3. #3
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,180

    Re: OnOpen event

    I must admit, I'm intrigued what FindAndReplace() does and how it differs from Replace()

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,096

    Re: OnOpen event

    Hi, Kyle123,

    +1.

    @Les:
    What about using InStrRev for the position of the last . in the string and the using Left with this number if the user has chosen to save a xls as xlsm?
    From the immediate window:
    Please Login or Register  to view this content.

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  5. #5
    Registered User
    Join Date
    03-20-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: OnOpen event

    Hi tehneXus
    Thanks for your reply. I obviously need to do some more homework on the referencing and assigning a sub, etc., and your input is helpful
    Les

  6. #6
    Registered User
    Join Date
    03-20-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: OnOpen event

    Hi Kyle123
    I knew someone would ask
    Please Login or Register  to view this content.
    I can't remember why I wrote this (it was in access, years ago) - can you see if it is any different from excel's Replace()?

  7. #7
    Registered User
    Join Date
    03-20-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: OnOpen event

    Hi Holger
    Good idea - reckon I'll do that - thanks
    Les

  8. #8
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,180

    Re: OnOpen event

    The replace function exists in Access IIRC, I suspect it does exactly what yours does, although I suspect somewhat more efficiently

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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