I am using Reafidy nice bit of code to hide worksheets if the user disables macros. The problem, though, is that it doesn't work for Chart Worksheets. How should I adjust this to accommodate charts?
Private Sub Workbook_BeforeClose(Cancel As Boolean)
bIsClosing = True
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wsArray() As Variant
Dim iCnt As Integer
Application. ScreenUpdating = 0
I have a sheet with macros that allows users to fill in data from columns F10 to O10 from row 10 to 54.
Column P will then make some computations based on the inputs from col F to O. Row 55 will also make some computation based on inputs from row 10 to 54.
How to I modify the code below to allow users to either add/delete columns and rows such that it does not fowl up the computations?
The computation does not have to be at column P or at row 55, they can be dynamic and change depending on how many rows and columns the users delete/add.
A little background on what the code does. From Column F to O, the code UNLOCKS the columns only if user inputs date in Cell 3 of that particular column.
Example. Column F is locked from F10 to infinity at the start. Only when user inputs date into F3 does the cells F10:F54 is unlocked for user to input data.
This goes on until column O. I want users to be able to add more columns, or delete ones they don't need.
Is there a way to hide a macro from the list where you choose which to run, but not in the VBA editor? The userbox I just created calls upon 2 different macros, and has a macro to bring up the userbox. I need a way to hide the macros in Module3 from selection, but keep the macros in Module4 available to choose to run.
I have created some macros and assigned shortcut keys. The workbook is shared by other users. I want to keep these macros from being accessible to other users. I know that they can go to ToolsMacros and display the names of macros and run them. How can I prevent them from accessing the ToolsMacros? Or can I assign password to individual macros?
If I put the macros in individual sheets as Private, they don't show up on the ToolsMacros list, but then I can't assign shortcut keys.
I update an Excel file daily and save it to a server on a network. Other network users access this file from their own computers. I have a macro created that opens a dialog box upon opening that shows users know of anything important.
Since employees in the office have Excel set to Paranoid mode or something like that, they are never even prompted that macros exist and are intended to run and that they should choose to allow them to run or not.
I would like to approach the problem in a way other than asking all employees to activate all macros or to lower their Excel security settings at all times or only when opening this single document. That would be considered too inconvenient.
I have been looking at Dave's Script found in this post Determining if macros are enabled It works just like i want it too. But when I try and use it in my workbook with differnt sheet names it doesnt work. I have even tryed to rename the sheets in the example file and it stops working. My sheets are as follows:
Sheet1 (JI) Sheet2 (GC) Sheet3 (ET) Sheet4 (SV) Sheet5 (JB) Sheet6 (P) Sheet8 (Info Sheet) <----This is where the Notice to turn on Marcos is Sheet9 (D)
I have the following code in Module2
Public bIsClosing As Boolean Dim wsSheet As Worksheet Sub HideAll() Application. ScreenUpdating = False For Each wsSheet In ThisWorkbook.Worksheets If wsSheet.CodeName = "Sheet8" Then wsSheet.Visible = xlSheetVisible Else wsSheet.Visible = xlSheetVeryHidden End If Next wsSheet Application.ScreenUpdating = True...................
I have a workbook that I would like to share and allow multiple users to add and make changes at the same time. However, the workbook is full of macros that really need to run. Is this possible? The workbook is written in excel 2003.
If this is not possible then is it possible to share the workbook in a similar fashion but not allow any other users to make changes, but update their workbook with the changes that I make?
I am totally new to Macros. I need a Macro which should format a column based on the value of another column.
Consider I have 10 rows. I have to format column D, based on the value of Column E. If the value of Column E is > 1000, then the background color of Column D should be changed as green. The most important requirement is Column E should be invisible, Changing the font color of Column E as White does not seem ok cuz when we select the sheet entirely using Ctrl+A, the white values are very much visible. Can this be achieved using a macro?
on sheet1 I have a button I need to do the following when clicked: (1) name the next 30 sheets based on cell values in sheet1 (2) for those 30 sheets, hide some of them based on a y/n input in sheet 1
To clarify: the worksheets do not need to be created, they already exist. They just need to be renamed and hidden based on that y/n criteria. see attachment with just 1 worksheet for clarification. So - The next 30 sheets are to be named by the following ranges (B7:B16), (B21:B30) and (B35:B44). For every product with a "n" in column C of sheet1, the worksheet for that product needs to be hidden.
I am having trouble making a module that can open in all Excel workbooks. It should ask user to enter column names and the data and should ask user to select a chart type and then produce a chart of the entered data?
My excel charts don't resize the way they should after hiding them through a worksheet macro. After unhiding, the scale overlaps the data labels and, over several times, the scale even colapses to a small thin line. Any suggestions on this? The code I'm using to hide cells and charts:
I’ve thought up a workbook with four sheets named “Employees Summary”, “EMP001”, “EMP002”, “EMP003” and a number of customer account sheets. Customer account sheets are named by their account numbers.
Now for the hard part. I will have three employees. All of us will have to login to the workbook with a username and password. Usernames would be. Administrator, EMP001, EMP002, EMP003. You should be able to change your password at any time.
All sheets will be visible to the administrator. Employees will only be able to see their respective “EMP” sheet and "Employee Summary" sheet. the rest is hidden. And the really hard part Every employee will enter all their transactions for the day into their respective sheets....
I am currently building a tool that works with an undefined range of input variables, e.g. I now have a range of 200 values but it could just as easily be 400 or 100. I am using these values for further calculations and have thus 'dragged down' to cells that in some instances are empty, resulting in values that are 0. I'm also building a chart based on these values, but it shows the values that are 0 as well!
Is there any way to just plot the non-zero values without changing the data range?
I've created a shared spreadsheet with multiple sheets. All the content was created to be visible without having to scroll at 87%. My question is can I protect or lock the zoom level so that when anyone else opens the spreadsheet, it will only be visible at 87%? I have my screen resolution at "Smallest" so not sure if that too will affect the viewing level.
A workbook with 7 sheets in it. The first sheet contains buttons with macros that when clicked will navigate you to different sheets in the workbook based on what button you click. Here's what I want to do. When the workbook is opened, I want it open to that first sheet with the buttons, but show no other tabs for the other sheets at the bottom. And then when that button is clicked on the first sheet to go to a different page, I want that page to be the only visible tab in the workbook with the rest still hidden. Is that doable? I already have the buttons and codes and everything for the navigation part down, I just need to have the other hidden when I'm on a different sheet.
Sub Button10_click() Worksheets("Account Type").Activate End Sub Sub Button11_Click() Worksheets("Name Change").Activate End Sub Sub Button12_Click() Worksheets("Address-Phone").Activate End Sub Sub Button13_Click() Worksheets("Main Menu").Activate End Sub Sub Button15_Click() Worksheets("Cust-Owner").Activate End Sub Sub Button16_Click() Worksheets("Misc").Activate End Sub
What i am trying to do: Create a login option to filter what worksheets each user can see. I need the users to be able to view 2 sheets not just 1. Also i need an admin login that can view all sheets. I want to keep the workbook protected so even though they can see and edit those two sheets they can only edit certain unlocked cells. Admin should be able to view/edit all cells on all sheets.
Below is my code which is made for a sample workbook. I was just trying to get the concept down and then i would convert it to the real workbook.
I wanted to know if there is any function/macro/option available which can hide/show sheets based on the value selected in the particular cell.
Basically i will have a table of contents as the first sheet. Post that if 'Yes' is selected against a particular line item, the sheet for the same should appear. If 'no' is selected the sheet would remain hidden. The sheets would be linked via hyperlink to the particulars (name) for each line of the table of contents. If required i can also remove the hyperlinking of cell.
I wrote a macro that hides every row that contains an "X" in column X for every sheet. The problem is that it runs very slowly (assumedly because it is checking every cell on every sheet). Is there a way to clean this up and make it run faster?
My code: Sub HideRows() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets Dim Cell As Range For Each Cell In ws.Range("X:X") If Cell.Value = "X" Then Cell.EntireRow.Hidden = True End If Next Next ws End Sub
As many are aware, there is an option to make a sheet very hidden. However, a "very hidden" sheet remains visible in the VBE project window. A user with basic VBA knowledge can easily selected the hidden sheet & go in the property window and change its visibility setting to "-1 Sheetvisible". Is there any way to make hidden sheet invisible in VBE as well?
I'm trying to create a code that when run hides a selection of columns and defines the minimum and interval value for a chart on the active worksheet. My attempt is assigned to the 'update skills' button at the top of the "GRAPH" worksheet of the attached workbook.
i have a list of services with "yes" or "no" options in the column beside it. Description for each service is given on separate sheets (Workbook sheet 1, Workbook sheet 2..etc). i want to format it in a way that if i choose "no" for one of the services, its description sheet hides.
I have a sheet that pulls data from an external source. I want it so that when a person clicks on a cell or a range of cells, it automatically locks and hides the formula. I don't want to lock it by using the conventional method because protecting it will cause the external data not to be able to populate the cells. So my thinking is that when the data is imported, and the user clicks on a cell, it will automatically lock and hide.
I need columns
A C E:BB to be locked and hidden as soon as someone clicks it. Is this possible?
I read somewhere that I may be able to do some type of "Private Sub Workbook_SheetSelectionChange" to achieve this.
I have a workbook that has about 30 different sheets with names titled "Joe", "Jane", "Paul", etc.
I have a Cell (B12) that has been formatted as a dropdown menu with about a dozen different options such as "Audit A" , "Audit B" or "Other" etc. What i'd like to do is hide or display certain sheets based on what the value is in cell B12.
if cell B12 says any of the following "Audit A" or "Audit B" then it would hide certain sheets. If the value in B12 says "Other" or something other than "Audit A" or "Audit B" then it wouldn't hide anything at all. I tried searching and couldn't find anything like this although it may have been answered before.
I have a worksheet that veryhides all the sheets but one when closes and when opens if unhides the ones a user needs. For some reason the quick key Alt + PageUp or Alt + PageDown doesn't work until each sheet is scrolled through by clicking on their tab with the mouse.
I have recorded a macro for a very simple line chart. In column A I have the X category and in column B the Y axis which is a linear function of X. The number of rows can be changed dynamically with a spin control, but I do not know how to dynamically modify the range inside the chart macro to enlarge or diminish the x axis. I am a professor of economics trying to use excel to improve my teaching.
