expand or collapse

[mac]

Hello,

I am not sure I am posting to the proper place. My boss gave me an
assignment and I am not sure how to go about it. I have a workbook that I
send to client and they fill out . There is 2 different types of data to do
different reports. I would like to set up a button or macro that if the
check either Med or Accts or Both that would show only the type of data for
each report. ex: if it only Med data then I want to hide Accts worksheets
and if it is Accts then I would like to hide Med worksheets. I hope I am
making sense.
Any help would be GREATLY APPRECIATED.

THANK YOU

 

[AltaEgo]

Depends exactly how you wish to do and how you wish to do it.

This will toggle the visible property of both sheets (i.e. hide one and
unhide the other) but will not make both visible at once. However if you
have a little experience writing VBA, it should give enough information to
write your own.

Sub ToglSheets()

If Sheets("Accts").Visible = False Then
Sheets("Accts").Visible = True
Sheets("med").Visible = False
Else
Sheets("med").Visible = True
Sheets("accts").Visible = False
End If

End Sub


Now you need your users to be able to access the code. This will show you
how to set up a custom menu.

http://www.ozgrid.com/VBA/custom-menus.htm

This tells you all you need to know including how to add your menu when your
workbook opens and delete it when your workbook closes.

 

[mac]

Hello,
I knew I was not explaining it right. I have 30 sheets in the workbooks.
Some are to input Med and some to input Acct. My problem is how to set it up
so that the first sheet I check if it is Med or Acct, if I check Med then
the sheets that have to do with Med stay open and the Acct sheets hide. The
sheet tab are all different . I have a checklist at the begining listing the
different sheets and what they are for ex. Salary is for Acct and PP is for
Med. I am so bad at explaining myself. thank you for your quick reply.

 

[AltaEgo]

OK.

If one sheet is always required to be visible, you could set up a cell that
allows the user to select either "Accts" or "Med" and hide or unhide based
on choice selected. You could do this by naming sheets to be hidden or
unhidden but, my preference would be to use a naming convention. For
example, If you name all Accts-related sheets with a first letter = A and
name all Med-related sheets with a first letter = M, the following will
hide or unhide as required:

The example presumes a value "Accts" or "Med" chosen from a data validation
listbox in cell A1 of a worksheet, the name of which does not start with A
or M.

- Copy the code below
- Right-click your main sheet tab (the one that is not to be hidden and the
name does not start with A or M)
- Click View Code
- Paste into the module
- Make sure your users main sheet start with something other than A or M
- Change all Accts-related sheets so they start with A
- Change all Med-related sheets to they start with M


Private Sub Worksheet_Change(ByVal Target As Range)
Dim X, Y
On Error GoTo Worksheet_Change_Error
Set isect = Application.Intersect(Target, Range("A1"))
If Not isect Is Nothing Then

X = UCase(Left(Target.Value, 1)) ' A = Accts M = Med


For Each Sh In Sheets

Y = UCase(Left(Sh.Name, 1))
If Y = "A" Or Y = "M" Then
' if the sheet name starts with A or M
If UCase(Left(Sh.Name, 1)) = X Then
' make visible sheets with the same first letter as
' the target cell of the main sheet
Sh.Visible = True
Else
Sh.Visible = False
' hide the one starting with the alternate letter
End If
End If
Next Sh
End If
Exit Sub
Worksheet_Change_Error:

Select Case Err

Case 13
'do nothing
Case Else
MsgBox Err & " - " & Err.Description
End Select
End Sub