I am trying to format all cells on all sheets (hidden or otherwise) as "Locked" so when the sheets are protected the user can't see the formulas. This macro individually selects every sheet in the book and applys the formatting. Is there a way to modify this code to accomplish the same thing without having it actually select every sheet? The only reason it is an issue is that after running the macro you end up on the last sheet in the book.
I have a button on a sheet that I want to unhide another sheet, but if this other sheet is already visible then I want this same button to hide it.
Here is what I tried and it does work except it runs all the IF's instead of stoping at THE IF that matches the condition.
If Sheet2.Visible = xlSheetHidden Then Sheet2.Visible = True End If If Sheet2.Visible = xlSheetVeryHidden Then Sheet2.Visible = True End If If Sheet2.Visible = xlSheetVisible Then Sheet2.Visible = False End If End Sub
I'm trying to create a userform that allows the user to swap sheets from visible to hidden status. I've made it to the point where all the sheet names are populated based on their current status in their respective listbox, but I am stuck on using the results of any swaps to newly set their visible property. the code I have in the userform is as follows:
I'm having a problem moving the cell cursor 2 rows down once I find my LastRow (see LastRow code below). My problem is I need to move down 2 VISIBLE Rows down and I'm running into it moving 2 Rows down whether they are Visible or Hidden? If Row 50 is my LastRow, and I have it move 2 rows down, if Row 52 is Hidden, it still moves to Row 52 which can't be seen by the user after I do my stuff to that row? I can't unhide any hidden rows for other reasons. Is there a way to have the cursor move down 2 VISIBLE Rows instead of 2 Rows regardless?
The code I use to select the LastRow is:
LastRow = Cells(4000, 8).End(xlUp).Row I move down 2 more rows using:
Cells(LastRow + 2, 8).Select 'Then I do some stuff here for the user, so it can't be a hidden row
I was working on a multi-tab spreadsheet (7 tabs to be exact) and I went to hide one of the tabs and all of a sudden the worksheet went away and I was left with my empty personal.xls spreadsheet. If I go into tools and visual basic I see my spreadsheet listed as VBAProject (Durex Sales Oct 1 to Oct 28 06.xls) with the 7 sheets listed below it within a folder called Microsoft Excel Objects, but I can't get it back in spreadsheet form.
I am using the following code to create a custom command menu.
Sub AddMenus() Dim cMenu1 As CommandBarControl Dim cbMainMenuBar As CommandBar Dim iHelpMenu As Integer Dim cbcCustomMenu As CommandBarControl On Error Resume Next Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete On Error Goto 0 Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")..........................
I want to add a new button control at the bottom of the menu underneath the holiday control but it keeps adding it on the sub menu, which leads off the holiday button control. I want the menu to look like this.
Open Net 2 Access Add Employee Edit Employee Delete Employee Holidays (3 options on sub menu) *New Control
i am using the macro below to get excel to hide all sheets but the one called "open"...
PHP Sub savemini()
For Each sh In ThisWorkbook.Worksheets If Not sh.Name = "open" Then sh.Visible = xlVeryHidden Next sh
ActiveWorkbook.Save End Sub
So the workbook is protected with the password "letmein", and the VB code above in theory should unlock the workbook, allowing all visible sheets to be hidden with the exception of the sheet called "open". Then is saves the file.
Now heres the odd bit... it works for certain people, but not for others. I have made sure those it wont work for are not doing anything weird and they are not.
The workbook i am using has a code that only opens certain sheets for certain users. I as a master user have access to all sheets. I can go into the users sheets and click the button that activates the macro above myself and it works fine, but for some users it wont work.
I have 3 columns of data that are being copied from a .csv file. The data is pasted into Columns B, C, & D. Columns C and D are hidden. So that the user does not have to unhide the columns in order to delete the data, I would like to make it so that when the data in column B is deleted, the data in columns C and D is also deleted.
there is some clever code to create a customized menu in a dynamic Add-Ins section of the Excel ribbon; this section disappears when the workbook is deactivated and re-appears when it is activated. The custom menu has 2 main items each of which can launch a macro. There is also a 3rd main item called "Next Menu" which cascades nicely to a sub-menu. That sub-menu can have several items of its own. Very cool code.
However, I am trying to add a 4th item in the main menu, say, "Next Menu2" which also cascades to a sub-menu. The problem I'm having is when I try to replicate this code to accomplish this, "Next Menu2" appears in the sub-menu of "Next Menu", rather than as a separate (4th) main item below "Next Menu".
I believe there was another post (not sure of the date, but has since expired) which asked basically the same question as mine. There was a reply with code saying "This should work" but when I tried it, it didn't help.
I want to create a submenu to an already existing user defined menu.
After creating and saving the sub menu using Tools ->Customize ,it works fine. But when
I reopen the Excel sheet it is not found.
I could not find any macro related to the original User menu.Also this menu gets displayed after the AUTO OPen macro in my excel has worked. This menu gets displayed for only that workbook and not for others.
I have been trying to find out a way to create Sub menu for that ,but could not?
I have a button on a sheet that runs a macro to unhide another sheet. That works, but I want the sheet made visible to remain forward. Instead, the button unhides the sheet and the sheet the button is on comes forward again. I am unable to figure how to keep the sheet made visible forward. Here is the macro 'as recorded'.
Is there any way you can use VBA to assign a key to toggle between two different cell formats? I want to toggle a cell from white border with black text to black border with white text and visa versa, with the same key or button. I've searched the posts but can't find this mentioned.
Need to correct code to resize all visible rows on a sheet based only on the text in the visible columns. I have tried the below code but when it resizes it is using the largest amount of text in the rows including that in the hidden columns.
im looking for a basic macro for workbook copying, all workbook(all hidden, unhidden, and very hidden) sheets.
i know how to do unhidden sheets of course however the hidden and very hidden are giving me some trouble...i need to also copy over all the macro's, buttons, and drop down box's as well..not just values.
I have two subs which I've been trying to combine into one. Essentially all that is changing is the slicer name and range value.
Here's what I have:
Code: Private Sub Workbook_SheetPivotTableChangeSync(ByVal Sh As Object, ByVal Target As PivotTable) Dim cache As Excel.SlicerCache Set cache = ActiveWorkbook.SlicerCaches("Slicer_Project_Type3") Dim sItem As Excel.SlicerItem Dim myString As String For Each sItem In cache.SlicerItems If sItem.Selected = True Then myString = myString & "," & sItem.Name
I have 2 sub, and I want to use the same variables that reference values on my spreadsheet.
for instance, I declare and set ws as worksheet("sheet1") in the first sub. in the second one, I also want to reference to the same ws. Instead of having to declare it twice, being redundant, what would be a good way to do this?
How can I have a sub repeat itself? I have a code and I want it to ask the user at the end if they would like to do it again. If so the whole sub needs to be repeated. I'm sure this has something to do with looping, but I don't know how to manage this.
I have many checkboxes (1 to 26) that I want visible or not, based on whether the cell states "n/a" (are in a column in a different worksheet). Rather than writing this code multiple times, I'm looking to condense the code.
I have created a few User Defined Types in a Main subroutine, but when I pass them into another sub the intelisense for the elements doesn't come up. I can type in the elements manually - and it all seems to work okay - but with a lot of types and elements I really want it to save me the effort.
I think I am confused as to how to declare a variable in one sub and use it as an input into another sub. I have attached some code below that assigns a value to two variables and then calls a sub that uses those values. I am brand new to programming so I guess I am confused how to implement this.
The below is a selection of some of the code i have written to organise data. The below works, but i'm not sure how/if I can make all the subs work in order automatically once I start the first Sub? I realise what I have written is probably pretty poor code so many need changed?
Sub move_to_all() Sheets("All").Select Cells.Select Range("A10").Activate Selection.Copy Sheets("Rest").Select Range("A1").Select ActiveSheet.Paste End Sub Sub Omni_Trades().....................
I have Three questions about debugging VBA code of an excel add-in.
-Breakpoints set in certain Public Subroutines are NOT hit. I have an application that writes data to the add-in. You cannot debug the app + the add-in. The add does things(e.g. writes data), then calls a main public macro in the add-in, then the addin takes over. If I comment out the main macro, the parent app halts, then I try to set breaks in the add-in.
After resumeing, some breaks are never hit. I know this because if I set a stack trace (output ing the func name to a temporary worksheet), I see these functions being hit. All the modules are public.
Usually in C, breakpoints are not hit because that code is NOT part of the address space of the program.
2. Very naive question (I am a C,C++ programmer)...some tiimes when I hit F5, the whole app runs, other times a list of macros comes up (just as if I had gone to the menu and went toolsmacros. Why is this?????.....placement of the cursor?????
3. If in the app I have 5 code modules, with two public subroutines each. HOW COME, IF I GO TOOLSMACROSRUN (same as what I get sometimes when I go F5), I ONLY GET A PARTIAL LISTING OF THE SUBROUTINES, ie NOT ALL 10?