In my sheet, I've got a command button that protects the sheet and unprotects it (code was thanks to this forum) and I'd like to make it so that when the sheet is protected, all the other command buttons are hidden (CommandButton1 to CommandButton5) and then when the sheet is unprotected again, the buttons all show up.
The reason for this is because when other buttons are clicked as the sheet is protected, it brings up the error and shows Debug as an option and then the user can click that to look at the code and password for the protect sheet button.
There's a sheet called "Main" and 38 other sheets that shows the data (these 38 sheets all have the exact same structure/layout). Let's say these 38 sheets are called "country1", "country2", .... "country38".
Based on user input in cell J10 in "Main", I want to show only the relevant columns in all of the 38 country sheets. Specifically,
If J10 in sheet "Main" = "Option 1", hide all columns in all 38 sheets except columns A to W.
If J10 in sheet "Main" = "Option 2", hide all columns in all 38 sheets except columns Y to AU.
If J10 in sheet "Main" = "Option 3", hide all columns in all 38 sheets except columns AW to BS.
If J10 in sheet "Main" = "Option 4", hide all columns in all 38 sheets except columns BV to CQ.
If J10 sheet "Main = "ShowAll", show all columns in all 38 sheets....
Cell J10 in "Main" will be a drop down bar with the 5 choices.....
I have a some simple code that doesn't seem to want to work ALL the time. Granted, sometimes it works but not always. The first code is to hide a bunch of columns and the second is to display those again. I put in the If/Then to avoid trying to hide columns already hidden (I thgouht that had to be done - true). Anyone see any problems with this code. The error I get is:
"Run-time error '1004': Unable to set the hidden propoerty of Range class. I get it at
If wb.Worksheets("Growth").Columns("ap:iv").EntireColumn.Hidden = True Then wb.Worksheets("Growth").Columns("ap:iv").EntireColumn.Hidden = False 'Error is on this line End If..............
Is there a way to create a shortcut/button by hiding or showing a column? Example: I want to hide or show the whole column E by pressing a assign button or shortcut.
I am trying to hide/show a static set of rows based on the value chosen within a combobox.
The combobox has a list of names and one blank. The blank is selected by default, which renders the rows hidden. When a user selects a name in the combobox the rows will be shown.
I got some help on this, which allowed me to hide the rows when the workbook was activated and when the worksheet was activated, but the code to show/hide the rows based on the combobox value is not working.
1. I'm looking for a better way to get a UserForm (frmParts) to show right from the start as soon as I open the workbook while hiding the application. I use the following when I initialize the application:
Code: Application.Visible = False frmParts.Show
2. Later on, I add some parts to my inventory, but if I don't make the application visible, it doesn't save the data. So, I have to make it visible and then hide it again. Is there a right way to accomplish the save without having to unhide/hide the application? This is the applicable code I used (ComboBoxes & TextBoxes involved):
Code: 'copy the data to the database Application.Visible = True Application.ScreenUpdating = False
I am trying to create a worksheet that has multiple radio buttons. Each radio button is linked to a Macro. I need there to be many buttons running down one side of the worksheet. Each button needs to perform a macro that is relevant to the cells in the same row that it is on.
Essentially what i am trying to do is make a macro that when the button is pressed copys data from H6 and paste it into B6. The button is situated above I6. I need a button for each row from 6 to 110.
While this macro is easy to create (i use the record button and then assign the macro to the button), i would have to do this 104 times and assign a new macro to each button.
Is it possible to change the color of buttons or command buttons? There does not seem to be any place that allows this under properties for buttons, although there does for command bars. However, I've tried recording a macro as I change the color, but nothing get's recorded so I'm not sure what the syntax would be.
I have a spreadsheet with several buttons and I'd like them to change colors as they are pressed so it's possible to see what you've already done. And then, as soon as any other cell on the sheet is changed, the buttons reset color.
I have a tab within a workbook where i want the user to copy a different spreadsheet into and then press a macro button to run some code, however after this button has been pressed i want this one tab to show any changes that have been made to it, i.e make the cell red or something just so i can keep track of manual adjustemnts.
I am currently having an issue with the formula below showing a zero when there is no data, now i am referencing from this cell to another sheet but even with the IF(ISBLANK inside the formula on the other page it still shows a zero which is messing up what i am trying to do on another page.
I have a cell that contains the =Day1!G4. It reveals what is on another worksheet, but I want to be able to have the next cell be: =Day2!G4. Is there any way that I can code this without having to physically type in every cell? I tried, =Day1+1!G4 but it didn't like that.
The formula that I came up with perfectly works well with clear values. Everytime there's a #NUM!, #VALUE! in my array, the result I want wont show up.
I am creating a spreadsheet system for storing school grades, and I have a sheet on my system which shows graphs of the students data, and on this sheet there are rather a lot of graphs, and its sort of a visual overload to see so many graphs on one sheet. Im just wondering if there is any thing which I could insert which would give the person using the system a choice of which class's graph they want to show.
I can't get the zero's to show up in the part number formula in the wooksheet. Also how do you get the zero's to stay when you type numbers such as (03,04,ect.) the zero always disapears. I need it to stay as a number and not text.
I placed an add-in (.xla file) with a " function" and a "sub" into C:Microsoft Office XPOffice10Library. When I go to Tools-->Add-Ins the Add-In doesn't show up. When I browse the file it says it already exists and still doesn't show up. The strange thing is that the "function" does work, but the sub (which is a sub worksheet_change) doesn't run. I also tried doing the steps as they are laid out in http://www.ozgrid.com/VBA/excel-add-in-create.htm and ended up with the same problem. The function works when I open up a brand new Excel application and use Book1, so I know the function isn't embedded in the sheet.
I have a vlookup that I have almost working but I'm a little stumped my the last bit. the formula I have is this =VLOOKUP(J4,AW:AY, 3, 0) and the only issue I have is that if there is nothing in J4, then I want nothing to appear in my vlookup cell, but it is currently showing #N/A.
Why the value is showing up as 0. I know it should be 4 but it is returning the value 0 when I run my code clean through. When I step through the code using F8 it shows up as 4 as long as I am on the tab where the worksheet is. Why my code is returning a 0.
VB: StartRow = 5 Dim CountX As Integer Dim LastRowX As Range CountX = 0
I am using dglinfo with if statements, and I know that the spreadsheet is updated but the only way I can show the value in particular cell is by hitting the fx. Is their a way to show all the values at once instead of going cell by cell?
I have a spreadsheet gathering information on vehicles. One of the pieces of information is a 'Date Off Strength'. For the vehicles still on strength I have the symbol '#N/A' showing,, this is linked to a sheet using a VLOOKUP formula, is there a way to stop this symbol showing
I have to compile spreadsheets as tables of various values. One column in particular is a column of ID numbers and the worksheet is very very long. I need a method for checking this one column for duplicate ID numbers.
I am entering text into a cell and as soon as I go over 241 chars, give or take, the data turns to a row of ####'s when I get out of the cell. Help says the limit is 32K and I have wrap text on. What am I missing? The data displays OK when the cell is current, but goes away when the cell is not . When I preview a print I have the ####'s. I am using Excel 2000.
I have a very large spreadsheet (almost 9000 rows). I have filters on all columns, however when I click to see all the results for the column under the filter, just to view the contents of the row, some are not there. Does a filter have a limitted amount of rows it can hold.
I've got a bunch of formulas that often are trying to divide by zero, hence this error in the cell. Is there an easy way to modify the forumla or format so that if a division by zero is attempted, the cell can show a zero instead of this error?
why formula's I type in to a particular worksheet show all the time(this is a workbook containing numerous macro's). I have checked under 'Options' that the show formula's box is not checked
I have a spreadsheet with a lot of rows and there are three columns that can have a one of two values in it. These values are constantly changing from row to row, not just like a yes or no sort of thing. However 80% of the time they are the same values in all 3 columns, but I want some way of quickly identifying when there is a difference between column G, I, & K which are my three column names. So is there a way of conditionally formatting this to make a difference highlight in red or something like that?
