Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    150

    Angry Unanswered: Weird problem...

    Hi,

    I have this macro that i've written in VBA and a button on my toolbar to start the macro. Everything runs fine, the first time. After that, when i re-open my file, the code in my macro is not the same anymore. One line of code has changed, nothing else. Of course this created errors when the time ro run the macros came....

    Even if i save the macro, re-assign the button and save the file itself, it will be able to run, once. After that, it reverts back to that single line of code that doesnt work.

    any ideas why this is happening?

    thanks!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    What version of Excel?

    Can you post the macro code?
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Nov 2003
    Posts
    150
    I'm running Excel XP with 2000 file format. I managed to figure out why the code was changing. I had to very similar files, and the code wasnt opening the one i wanted.

    However, i would still like to know how to create a button to start this code directly on the sheet, instead of on the menu bar.

    Here is the code i have so far:

    ---------------------------------------------------------------

    Sub SaveMe2()

    Dim fname1 As String
    Dim fname2 As String

    With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
    End With

    ' Save as Range Name
    fname1 = Sheets("invoice").Range("N57")

    ' Save as user Input
    fname2 = InputBox("Please enter the target Filename", "Filename Entry")

    With ActiveWorkbook
    .SaveAs Filename:="\\ADMIN\My Documents\Comptabilité\FactureClients\" & fname2 & ".xls"
    .SaveAs Filename:="C:\Documents and Settings\Mr.Crud\My Documents\E-Comm\Audio Occasion\Factures Clients" & fname2 & ".xls"
    End With


    ActiveWindow.SelectedSheets.PrintOut Copies:=3, Collate:=True


    Workbooks.Open Filename:= _
    "C:\Documents and Settings\Mr.Crud\My Documents\E-Comm\Audio Occasion\Test\Factures Clients.xls"

    Workbooks("" & fname2 & ".xls").Close True


    End Sub

    ---------------------------------------------------------

    Cheers!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    There are several ways to do this. Perhaps the easiest is to choose an AutoShape (drawing toolbar). After you draw it and place where you want it, then right-click, and choose "Assign Macro". The macro listing will appear, and you can select it in the list.

    Or you could do the same with a text box.

    Alternatively, you could use Forms toolbar, or Control Toolbox toolbar. Forms is easier, Control Toolbox offers more formatting options.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Nov 2003
    Posts
    150
    Kool, i knew it had to be pretty simple, just no clue where to start looking.

    I have another question though. Since i want to protect most of the cells in the worksheet in order to prevent design changes, i need to lock the cells i dont want my users to change. However, this prevents my user from clicking on the image i assigned my code to....

    any solutions?

    oh yeah, and i need to open a *.txt file from another button on my worksheet. I dont know the VBA command to achieve this...

    thanks!
    Last edited by MrCrud; 01-30-04 at 17:06.
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  6. #6
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Originally posted by MrCrud
    Kool, i knew it had to be pretty simple, just no clue where to start looking.

    I have another question though. Since i want to protect most of the cells in the worksheet in order to prevent design changes, i need to lock the cells i dont want my users to change. However, this prevents my user from clicking on the image i assigned my code to....

    any solutions?

    oh yeah, and i need to open a *.txt file from another button on my worksheet. I dont know the VBA command to achieve this...

    thanks!
    just a thought but why dont you add a command button to the workbook instead of a image this is not affected by locking cells and then just call whatever procedure from the button.

    to open the text file wht dont you use something like this
    Code:
    Dim MyFile as String
    
    MyFile = Application.GetOpenFilename "Text Files(*.txt),*.txt"
    workbooks.OpenText MyFile
    The OpenText entry contains options that let you specify how you want the file to open like the Wizard that appears when you try to open a text file in Excel

    Hope this Helps
    David

Posting Permissions

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