Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
expand or collapse
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 -- thank you mac |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
expand or collapse
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. -- Steve "mac" wrote in message ... 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 -- thank you mac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
expand or collapse
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. -- thank you mac "AltaEgo" wrote: 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. -- Steve "mac" wrote in message ... 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 -- thank you mac |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
expand or collapse
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 -- Steve "mac" wrote in message ... 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. -- thank you mac "AltaEgo" wrote: 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. -- Steve "mac" wrote in message ... 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 -- thank you mac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i expand and collapse rows | New Users to Excel | |||
Expand Collapse Buttons | Excel Discussion (Misc queries) | |||
Expand/collapse | Excel Programming | |||
Expand and Collapse groups | Excel Programming | |||
expand/collapse row button | Excel Discussion (Misc queries) |