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?

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

--


Gary


"Woody" wrote in message
...
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?

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?

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?