+ Reply to Thread
Results 1 to 22 of 22

Automated Vba Macro

  1. #1
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,076

    Automated Vba Macro

    Hello to All Forum Members,

    I am using Windows 93 and have searched everywhere but cannot find any sample code of how to write a macro that would do any of the following:

    1. When opening Excel a commandbutton with no caption must appear.
    2. The user then enters a caption on the command button - to indicate to the
    command button what the user want the macro to do.

    For example:

    A. Say the user enters on the Button create "Stock Sheet" - The macro must create the necessary Sheets with complete detail, controls, checks and so on.
    B. Create a "Parts List"
    C. Production Scheduling Forecast
    D. Invoicing, Timesheet, Etc. Etc.

    O yes, any WorkBook should have no more or less than 401 Sheets, each with the appropiate tab color, conditional formatting and preset print ranges.

    I really thought that Excel was such a fantastic pliable tool with almost no limits, and yet
    nobody seems to have this readily available for me to use.

    Could anyone of you please be so kind as to write me such a Macro? People I can immediately think of to do it for me would inter alia be, Leith Ross, Daddy Long Legs, SHG, NBVC, Andy Pope and/or anyone else.

    I do need it quite urgent please.

    Thank you!

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Automated Vba Macro

    Are you having a laugh? Make an effort your self first!

    It's a bad concept anyway - relying on the user to add a caption to a button
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Valued Forum Contributor
    Join Date
    07-21-2008
    Location
    London, UK
    Posts
    326

    Re: Automated Vba Macro

    Captions Labels etc. all descript naming’s should be set and not easily changed, yes this could be done with VBA code, tricky but doable not that I recommend it unless you can admin the code as required easily

    ie if you code as you go and change stuff codes blow up and you sit wondering why

    Now it might be useful to have two buttons and change the visible button to the one required and so the code running behind that button is linked to each button (or not, if statement or case will cover this)

    Maybe if you had a report to run
    Then the button is Green with the text done, V's Red button with report not run
    And this could be checked on the close event and open event etc, kinda like using them as user on demand but flags also, bit like second reminder

    As for user editing.. Not so sure

    Window 93, so not sure version of Excel your running couple be one of a few

    jiuk

  4. #4
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Automated Vba Macro

    Sounds perfectly straightforward to me, but I'd recommend avoiding putting strain on the user by making them type in what they want Excel to do. Rather I'd recommend a simple iterative macro which, on every loop, does something random (e.g. inserts a new sheet, or puts in a header, or changes the formatting of a cell, or creates a formula, etc.)

    After each iteration simply display a non-modal message asking the user if the sheet looks like they want it to and keep looping until they indicated that yes, it does.

    That could barely be 10 minutes work, and I'm surprised that none of the people you mentioned have taken the trouble to write it for you yet.

  5. #5
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Automated Vba Macro

    @Winon - I've made a start for you ... just tweak this to fit your requirements:

    Please Login or Register  to view this content.

  6. #6
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,363

    Re: Automated Vba Macro

    Hey, that will even work in 2008 for Mac!

  7. #7
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,076

    Re: Automated Vba Macro

    Hi teylyn,

    Andrew-R gave me some code to use but, it does nothing !

    I have even tried to tweak his code to something like

    Sub DoAnything()

    'Ask the user what they want to do and do it
    With DoAnything
    .Vlookup(Search(Trackdown(Locate OR Find,Andrew-R OR teylyn,excel_forum,www,FALSE)))
    If(Vlookup(Search(Trackdown(Locate OR Find,Andrew-R OR teylyn,excel_forum,www,FALSE)))=True Then
    Insist on required Macro Assistance
    End If
    Thank Contributor
    End With
    End Sub
    But it also does not work...

    All I really need is a macro to create or write whatever programming is required. I do appreciate and thank Andrew-R for his attemp to help me but, he missed what I am actully asking for. Hopefully, he will have another go at it. Since you seem to be on quite a good footing with Roy, maybe you could consider coaxing him for a more elaborate contribution.

    Many Thaks

    Have a lovely Day

  8. #8
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,363

    Re: Automated Vba Macro

    I think the problem lies with your regional settings. If you need my input you should change your system to German settings. Note, though, that this requires an additional effort for punctuality and correct punctuation.

    Then try semicolons instead of commas in your formulas, and if that does not work, hit Ctrl-Alt-Delete and click the upper right button in the panel of six (Windows XP)

    cheers,

    [Edit: corrected punctuation errors]
    Last edited by teylyn; 10-02-2011 at 05:48 AM. Reason: corrected punctuation errors

  9. #9
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Automated Vba Macro

    Quote Originally Posted by teylyn View Post
    I think the problem lies with your regional settings. If you need my input you should change your system to German settings. Note, though, that this requires an additional effort for punctuality and correct punctuation.
    Surely if you change to the German settings you will need to alter the code thus...

    Please Login or Register  to view this content.

  10. #10
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Automated Vba Macro

    Use Code Tags not Quote Tags

  11. #11
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,076

    Re: Automated Vba Macro

    You guys ABSOLUTELY ROCK !!!

    Thank SOOOO MUCH for your wonderful sense of humour!

  12. #12
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,076

    Re: Automated Vba Macro

    OOOPS,

    Truly sorry 'bout that royUK, I just got carried away with the unbelievable sense of humor amongst you guys on this excellent Forum.

  13. #13
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Automated Vba Macro

    Everyone seems to have overlooked the fact that you are on Windows 93. Since that was only available as an OEM install on the old Etch-a-sketch, your Excel options are limited to Excel -3 and iExcel, which only have the original XLSK macro language. Therefore you will need to find someone who still writes Sanskrit. Also remember that you can only enter the macro functions as data labels on the invisible macro charts.
    Remember what the dormouse said
    Feed your head

  14. #14
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,363

    Re: Automated Vba Macro

    Coming to think of it, I can't really remember Windows 93. Was there ever such a version? I developed crappy database solutions in a tool similar to dBase when Win 3.1 was the rage, and I think the IBM Token Ring network we later ran used something like 3.11 for workgroups, but as far as I remember, the next rung on the ladder was Windows 95. The ultimate user experience with multi-media tools and lots of other cool stuff.

    Is this old age or did I miss a critical version?

    (or am I being had?)

  15. #15
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,076

    Re: Automated Vba Macro

    Hi teylyn,

    No you are not being had ! Actually you are quite right. It was in fact Windows 3.1 and 3.11 way back then, and to come and think about it, I must have referred to it as Windows 93, since it was around that time that Windows really started to take the South African market by storm. My very first PC was a 286 with a massive 40MG HDD & an awsome 16Kb RAM - Sounds like DLL's forte since he is hinting that I may approach him to write me my Macro in Sanskrit - LOL

  16. #16
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,076

    Re: Automated Vba Macro

    Sorry DDL, I mistakenly quoted your name instead of RomperStomper's

  17. #17
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Automated Vba Macro

    Not to worry - ask DLL anyway: about time he learned macros...

  18. #18
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,363

    Re: Automated Vba Macro

    Sounds like DLL's forte
    That was romperstomper, not DLL.

    I've just looked up the Windows time line, so I can sleep soundly and not worry about missed Windows versions. What a relief.

    I bet your massive 40 MB hard drive had to be partitioned, because the OS could not handle partitions greater than 32 MB, right?

  19. #19
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Automated Vba Macro

    I'd have thought the combination of Etch-a-sketch and Sanskrit was a giveaway...

  20. #20
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,363

    Re: Automated Vba Macro

    Yeah, well, I think at the time I was doing that stuff, Etch-a-Sketch had not been invented yet. If you'd mentioned punch cards, that would have rung my bell

    I threw out the punch cards of my first IBM mainframe code (sentimental keepsakes) when we shifted to the other end of the world, but they sure were fun to work with. Especially when somebody kicked the deck to the floor by accident and it had to be put in order again.

  21. #21
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,076

    Re: Automated Vba Macro

    Yes teylyn you are right, and then one also had to back up files on the old big Floppy Disks or use a tape backup - I promise to copy you with romperstompers' Sanskrit code!

    Have a lovely day you guys.

  22. #22
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Automated Vba Macro

    I doubt a 286 PC only had 16Kb of RAM, especially one with a 40Mb hard-disk (a decent spec for a 286).

    512Kb or 640Kb seems much more likely.

    BTW, I think that Windows '93 was one of the development names used for what finally became Windows '95, but I could well be wrong. I was working for P&G when Windows '95 came out and I was the first UK employee to run it (and the 2nd UK employee to have a Pentium class PC - a storming P75).

+ 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