Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: VB Code For Saving

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello I Have A Button On My Excel Template I Need To VB Code To Do The Following

    >Save The Template With The New Data Added On It To An Excel Workbook

    >Every 250 Times This Is Done On The Template Have A Message Box Saying A Customised Message.

    Please Can You Help, I have 2 Weeks To Do This And Much More So I Don't Have Time To Learn Visual Basic Thank You.

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    >Save The Template With The New Data Added On It To An Excel Workbook
    Do you want to save the existing workbook with changes or save the worksheet to a new file? If you want to save the worksheet to a new file, do you have a specific file name in mind or do you want to prompt the user for a file name?


    _________________

    Barrie Davidson
    My Excel Web Page

    [ This Message was edited by: Barrie Davidson on 2002-03-09 12:07 ]

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi It's Template And I'd Like It To Save To A New Workbook When New Data Is Added In and I want To Prompt The User For A File Name Thanks.

  4. #4
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can try this code:

    Sub SaveMacro()
    ' Written by Barrie Davidson

    'Variable for the new file name
    Dim NewFileName As Variant

    'Get the name of the new file
    NewFileName = Application.GetSaveAsFilename _
    (filefilter:="Microsoft Excel files (*.xls),*.xls")

    'Exit the sub if the user selected cancel
    If NewFileName = False Then Exit Sub

    'Copy the active sheet, save it
    'using the new file name, and then
    'close it
    ActiveSheet.Copy
    ActiveWorkbook.SaveAs FileName:=NewFileName, _
    FileFormat:=xlWorkbookNormal
    ActiveWorkbook.Close

    'Increase the counter by 1
    Sheets("Counter").Range("A1").Value = _
    Sheets("Counter").Range("A1").Value + 1

    'Display the message box if the value
    'of the counter is >= 250
    If Sheets("Counter").Range("A1").Value >= 250 Then
    MsgBox prompt:="Your custom message"
    End If
    End Sub


    Regards,
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Arash, don't take this the wrong way, I don't mean to sound patronising or condescending .

    If this part of your A-Level is to be examined (ie not just course-work), it'd be a good idea to go through Barry's code in the VBA editor and press F1 for help on any of the code statements you're not sure of.

    I came unstuck during my Computer Studies O-Level (showing my age there), when I used a piece of code from a friend in my program and couldn't explain what it meant. I ended up being docked 10% for my project...

  6. #6
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-09 12:47, Mudface wrote:
    Arash, don't take this the wrong way, I don't mean to sound patronising or condescending .

    If this part of your A-Level is to be examined (ie not just course-work), it'd be a good idea to go through Barry's code in the VBA editor and press F1 for help on any of the code statements you're not sure of.

    I came unstuck during my Computer Studies O-Level (showing my age there), when I used a piece of code from a friend in my program and couldn't explain what it meant. I ended up being docked 10% for my project...
    That's a really good point Mudface.

    Took a look at your profile - your rugby interest now explains the moniker I've never played the game (actually don't even understand it), but it sure looks like a walk on the wild side

    Take care MudFace and keep up the great work on the board.
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  7. #7
    New Member
    Join Date
    Feb 2002
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi, There Is One Problem With When I Save It, This Error Message Comes Up Run Time Error '9' Subscript Out Of Range. I Go to Debug And This This Is Highlighted In Yellow


    Sheets("Counter").Range("A1").Value = _
    Sheets("Counter").Range("A1").Value + 1

    Could You Help ? Thanks
    Arash

  8. #8
    New Member
    Join Date
    Feb 2002
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I don't Mind If I Get An 'E' For This Project, I just want it done so then I can start revising On my History And Other Subjects, I'll Pull My Exam Grade On This For the Theory, I would learn VB if I had The Time

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    There should be a sheet named "Counter" for this code to work. Do you have one ?
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  10. #10
    New Member
    Join Date
    Feb 2002
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    No How Do I Do This?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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