+ Reply to Thread
Results 1 to 4 of 4

Make a Workbook into VB Program

  1. #1
    Woody
    Guest

    Make a Workbook into VB Program

    Hey all,

    I have created (with some great help here) a Workbook with a few sheets and
    all my formulas and functions are working great. Thanks to thoes that helped
    me.

    Now as a final step I want to make into a Program or a Macro so it does not
    look like a spreadsheet with all the toolbars, grids, row #, column #, and
    etc. Is there a easy way of doing this?



  2. #2
    Gary Keramidas
    Guest

    Re: Make a Workbook into VB Program

    you can turn a lot of those off under tools/options/ window options

    --


    Gary


    "Woody" <[email protected]> wrote in message
    news:[email protected]...
    > Hey all,
    >
    > I have created (with some great help here) a Workbook with a few sheets
    > and
    > all my formulas and functions are working great. Thanks to thoes that
    > helped
    > me.
    >
    > Now as a final step I want to make into a Program or a Macro so it does
    > not
    > look like a spreadsheet with all the toolbars, grids, row #, column #, and
    > etc. Is there a easy way of doing this?
    >
    >




  3. #3
    Roy Wagner
    Guest

    RE: Make a Workbook into VB Program

    Woody,

    Open a new workbook, add 2 command buttons, and paste the code below into
    the module. Rename your buttons or subs to match.

    It works like this. The HIDE STUFF button procedure checks all of your
    toolbars to see which ones were currently visible. My collecton has 93
    toolbars, even though I only keep a handful visible. If the toolbar is
    visible, it's name is added to a vertical list that begins in cell A1, and
    then its visible property is set to false. The scroll bars, sheet tabs and
    other stuff from the Tools/Options/View are simply recorded macro code pasted
    into the procedure. They are arbitrarily set to visible=false without seeing
    what the original state was. Alternatively, you could survey these as well
    and store the ones that were visible on the sheet. I usually keep all of
    those options on so this works fine for me. If you are going to distribute to
    many, would probably be nice to go the extra mile to be able to leave it the
    way you found it.

    The UNHIDE STUFF button procedure naturally reverses the effects of the
    HIDE STUFF button by reading the list in Column A and making all of the
    tools/options/view items visible. Of course, if there were view options you
    didn't have checked, they'll be checked now! Finally, the toolbar names are
    removed from the A column. You could adapt this to store and retrieve the
    data to wherever you want, including an external sequential file without much
    additional fuss.

    I did not see an obvious way to hide the Worksheet Menu Bar, which generates
    an error when you try to hide it using the below procedure, thus the
    exception in the code. I'm not sure if you would want to hide that anyway, or
    if it is even possible.

    I would probably keep the Hide/Unhide buttons through development, but in
    practice run the hide procedure when you open the workbook and intercept the
    close process to restore everything. I didn't have time to experiment with
    that aspect this evening, however I am working on a project that I will be
    also be making look less like Excel and more like VB, so probably after
    vacation I'll get to that. Not sure what I'll do without a computer for a
    week. Yikes!

    Roy Wagner


    Private Sub cmdHideStuff_Click()

    Dim x As Integer
    Dim xlToolbar As CommandBar

    x = 1
    For Each xlToolbar In Application.CommandBars
    With xlToolbar
    If .Visible = True Then
    Cells(x, 1).Value = .Name
    If .Name <> "Worksheet Menu Bar" Then .Visible = False
    x = x + 1
    End If
    End With
    Next

    With ActiveWindow
    .DisplayHeadings = False
    .DisplayHorizontalScrollBar = False
    .DisplayVerticalScrollBar = False
    .DisplayWorkbookTabs = False
    End With

    With Application
    .DisplayFormulaBar = False
    .DisplayStatusBar = False
    End With

    End Sub

    Private Sub cmdUnHideStuff_Click()

    Dim x As Integer
    Dim xlToolbar As CommandBar

    For Each xlToolbar In Application.CommandBars
    x = 1
    Do While Cells(x, 1).Value <> ""
    If xlToolbar.Name = Cells(x, 1).Value Then
    With xlToolbar
    .Visible = True
    End With
    End If
    x = x + 1
    Loop
    Next

    With ActiveWindow
    .DisplayHeadings = True
    .DisplayHorizontalScrollBar = True
    .DisplayVerticalScrollBar = True
    .DisplayWorkbookTabs = True
    End With

    With Application
    .DisplayFormulaBar = True
    .DisplayStatusBar = True
    End With

    x = 1
    Do While Cells(x, 1).Value <> ""
    x = x + 1
    Loop

    Range("A1:A" & Format(x)).Select
    Range("A1:A" & Format(x)).ClearContents
    Range("A1").Select

    End Sub




    --
    (delete .nospam)




    "Woody" wrote:

    > Hey all,
    >
    > I have created (with some great help here) a Workbook with a few sheets and
    > all my formulas and functions are working great. Thanks to thoes that helped
    > me.
    >
    > Now as a final step I want to make into a Program or a Macro so it does not
    > look like a spreadsheet with all the toolbars, grids, row #, column #, and
    > etc. Is there a easy way of doing this?
    >
    >


  4. #4
    Roy Wagner
    Guest

    RE: Make a Workbook into VB Program

    Oops! Forgot to pop the gridlines....

    ..DisplayGridlines = False 'to hide
    ..DisplayGridlines = True 'to unhide

    Paste in the revised With/End With loops below.

    Roy
    --
    (delete .nospam)

    With ActiveWindow
    .DisplayHeadings = False
    .DisplayHorizontalScrollBar = False
    .DisplayVerticalScrollBar = False
    .DisplayWorkbookTabs = False
    .DisplayGridlines = False 'added this line <<<<<<<<<<
    End With

    With ActiveWindow
    .DisplayHeadings = True
    .DisplayHorizontalScrollBar = True
    .DisplayVerticalScrollBar = True
    .DisplayWorkbookTabs = True
    .DisplayGridlines = True 'added this line <<<<<<<<<<
    End With



    > > Hey all,
    > >
    > > I have created (with some great help here) a Workbook with a few sheets and
    > > all my formulas and functions are working great. Thanks to thoes that helped
    > > me.
    > >
    > > Now as a final step I want to make into a Program or a Macro so it does not
    > > look like a spreadsheet with all the toolbars, grids, row #, column #, and
    > > etc. Is there a easy way of doing this?
    > >
    > >


+ 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