I would like to hide the content of a cell so it appears blank to user. I edit excel bike tour (route) cue sheets for our bicycle club and some info is needed for formulas in calculating total distance but doesn't need to be seen by the bicycle rider.
View 5 RepliesIn which control of vba can I show output of my work to a user of the program? But there is a trick here. In the output form, I need to ask something to the user so that I can proceed. (I need to put a few labels and a text box in the output form)
View 9 Replies View Relatedis there a way to format a cell so that when i enter a time like 1 pm the pm does not show? this is for a schedule that will calculate the sum of hours employees have worked so i can't format the cell as text. also, i want to avoid military time (no 13:00).
View 9 Replies View RelatedIf I have data in range A1:Z99 I want to write a macro to hide all the rows that have a zero in the A column.
View 9 Replies View RelatedI'm sure this is an easy one, but I have never done this before. I need to know the best way to hide (for example) Columns U:W if cell U3 is blank.
View 9 Replies View RelatedIs there a way to get Excel to look down a column for a particular value and where that value appears hide the row it appears in?
We have a series of reports which all have the same template, however we don't want some customers to be able to see some of the products as these are own-label products for which aren't offered to everyone.
I'd like to add this as a macro to a combo-box so that the user can view a report for their customer and then send a copy to the customer with the knowledge they won't see anything we don't want them to...
I have 2 worksheets, let's call them "Sheet1" and "sheet2".
Sheet 1 has 2 columns (A, and B)
Ie. "Sheet1:"
Col A, Col B
Red
Green Yes
Blue
Yellow
Orange Yes
For each entry in Column "B" that has a "Yes" value, I need to copy the color value in column "A" into the next available empty cell in "Sheet2" in column A.
"Sheet2"
Col A, Col B
Red 3
Blue 5
Yellow 6
Final Result:
Col A, Col B
Red 3
Blue 5
Yellow 6
Green
Orange
how to do this particular challenge? For Sheet2, I think I'll need a function to determine the first available blank cell in column A.
I am trying to use a MIN() function on a row of numbers; the row is not completely full (some days data was not recorded) and when I have a blank cell, the result in the MIN() frame is 0.0. This is unless of course I have a negative number (which I don't, but for the sake of argument)
This is my current function:
{=MIN(IF(AND(MOD(COLUMN($D10:$DL10),5)=1,CELL("CONTENTS",($D10:$DQ10))0),$D10:$DL10))}
It works like:
Take every 5th column starting at the second.
Try to ignore any cell that contains 0. (This I am trying to get to filter out the empty cells; I have tried ISBLANK(), ISNUMBER() and still get nothing)
Then return the cell to the MIN() function.
That is the long winded thing of it all, basically it can be summarized as this:
{=MIN(D10:DL10}
I have also tried some other code which I was told did the same thing essentially (which it does) but is shorter and nicer:
{=MIN(IF($B$5:$DQ$5="Q*",$B10:$DQ10))}
Which will check for the proper heading on each Column.
These functions work fine with AVERAGE/MAX as far as I can tell, just not with MIN().
As a side note, none of the cells leading up to this function contain "0".
I am linking several cells in Sheet1 to other sheets throughout the workbook. Sheets2-10 to be exact. The problem is when no data is in a given cell in Sheet1 a '0' shows up on the linked cell in Sheet2-10. The problem is 0 can be a correct response sometimes and if 0 is always to show up someone is going to misunderstand the task.
I just want to stop blank entries from being linked over as 0. I have a ton of cells being linked and don't want to do conditional formatting on each individual cell. Is their any easy way to stop this?
I have a user form that has a combo box "City" two text boxes one called "Flight" and the other "Date". What I'm trying to do is to prevent the user from saving the data input from the user form if any of those three fields is left blank. The code that I have so far checks all of the required fields, if any are left blank a message notifys which field(s) is left blank and return the focus back to that field. But the rest of code also fires.
What I really need is either to stop the code if any fields are blank and return the focus back to the blank field, the user completes the field(s) and clicks the save again, or better yet, pause the code until all the required fields are completed and then complete the save. (There is actually another 200+ lines of code in this sub, but I deleted it to keep the post a little shorter.)
I want to hide the rows if 3 cells in column B to E is empty (see Result)
Original Table
A
B
C
D
[Code]....
I'm using pivot table and i want to erase the blank cell that come out with pivot table and replace it with number 0 or -, how can i do that ? i've be try to do this by go to the table option and in the empty cells check list i've put 0 or - but the pivot table still come out "blank".
View 8 Replies View RelatedI am creating a worksheet for my buisness and I have input date of birth and have done a formula to work out ages but I dont know how to hide the formula and leave the cell blank so that when I create new information it automatically puts the age in.
View 1 Replies View RelatedI'm trying to create a table that adjusts dynamically to how many cells in a row have text in them. In other words, when a cell is left blank, the entire row should not appear in the table.
I've tried the following code:
Sub test()
Dim i As Integer
Dim nrrows As Integer
[Code]....
When I run this code, it hides all rows, even the ones that aren't blank. why this might happen and how I can make it work?
I have a row in a table from columns C to CZ. All the cells in the row contain a formula, between 1 and 204 cells in the row will have a value (i.e. will not be blank).
The cells with a value will start at column C and may or may not have a blank cell before no more values and blank cells to the end (Col GZ).
Example:
C D E F G H I J K >..........................GZ
23 34 67 74 2 34 6 2 56 all blank ("") to end
or:
C D E F G H I J K >..........................GZ
23 34 67 "" "" 34 6 all blank ("") to end
I need to hide the entire columns when the cells in this row are blank but NOT if the blank cell has valued cells after it (i.e. do not hide columns F and G in the second example.
I can do this by looping back from col GZ and hiding the columns one at a time, which is very slow. I am stuck on the code to select all the relavent columns and hide together.
i have a set of conditional formatting set up for my workbook, the first is simply that if the result is false the cell is blank(white text,white background). the second if positive result,keeps the text black till 1 year has passed. the problem is the third, i.m trying to allow the user to blank out the cell within the year (white text,white background)if certain criteria are met. the idea is that they change the entry in cell D from T to TX or from M to MX and the formula pick this up, but i keep getting a error saying "your formula contains an error", the formula im using is, =if(or($D5="tx","mx")) then i set the format to (white text,white background). its probably really simple but i,ve been trying to solve this now for around 8 hours and i.m stuck
View 2 Replies View RelatedI have a worksheet that contains 15 instances of a repeated table over 700 rows. Each table is 45 rows in size and is housed in between the natural page breaks in the spreadsheet.
These tables are populated from data form another worksheet but may not all be used (8 out of the 15 may be used but will always start from table 1 and there will be no missed tables).
In the very top right of the table is a cell value that is only displayed if the table is in use, so will be blank if not used.
Code that will hide multiple rows (45) based on a cell value being blank.
i have created a spreadsheet to simplify our work flow, I am stuck on what is probably the easiest of the commands.
basically have rows dedicated to specific codes and the colums represent values relating to each code, all codes have a different set of values, the attached example only has a few variables but the actual worksheet will have several hundred.
the idea is the user will input the code they wish to get details on in A2 and then press the command button and it will then show (as per the after sheet in the attachment) just the relevant information for that code, so filter the code in column A and hide the columns which hold no value.
where i am getting stuck is I am not sure the best way to proceed, is it best to create the macro button to do the filter and hide or is there a better way using vlookup and a pop up window asking for the relevantcode to be inputted to to retrive the information, again understand there will be hundreds of colums and hundreds of rows and the values may be 20 or 30 colums apart for some of the Codes so this simplification is really saving the user a lot of time.
I use this to read cell content, add some text/characters (ie. [ and ]) and change the properties of the complete cell
Sub COMMENT()
Worksheets("DVD Lijssie").Activate
If ActiveCell.Value 0 Then ' Change all in to ... ... ...
ActiveCell.FormulaR1C1 = ActiveCell.Value & " " & "]" & " " & "["
With ActiveCell.Font
.Name = "Arial Narrow"
.Size = 8
.ColorIndex = 16
End With
End If
End Sub
HOW can I change this vba-code so it leave's the content of the cell like it is and add some content with the use of let's say TexBox1 and ONLY use different font properties for the newely added content?
Formula/macro/etc that would enable me to have content of a cell changed based on the content of another cell in the same row.
Example: cell in column D says "PSA" - so I would need the cell in column H for that same row to read "Radio"
I would need an entire sheet scanned to review for these occurrences and make the appropriate changes. I also would need the formula to be inclusive enough to scan for variations in column D cell content (PSA 1, PSA 2, etc).
i attach my sample excel file here, book1.xls. sheet1, "input". it check the column "C" row by row and if it detect the different content data in next column, it will auto insert a new row. it will ignore the space. below is my coding:
View 3 Replies View RelatedI have a challenge in excel functions, I need to save a file as common folder but in that workbook have multiple sheets need to show the sheets as per the user name on system other sheets want to hide with password protect,
example,
file name:- Report
Sheets:- A,B,C,D
if now using system user name is A then need to show only "A" sheet, other sheets want to be hide and the same for all.....
What is the code to hide my vba calulating on screen? I have vba doing a bunch of things (like updating values etc.) that I don't want the user to see while using the spread sheet.
View 2 Replies View RelatedI developed some pivot table reports and macro'd out pdf'ing them for the users but now management want the users to be able to manipulate the data themselves !! The file is 31 sheets and I'm figuring they'll only want to see the sheets that are relevant to them so is there a way to macro it out?? Perhaps when the file opens it asks, "Who do you want to see?" Jack? Joe? or Rudy?, Month or YTD, or Both? or Everything? and it will hide the sheets that are not selected. The words "month" and "YTD" is in Cell "N1" and the individuals names are in Cell "M1".
View 6 Replies View RelatedMacros that are active in a workbook can typically be viewed by going to Tools->macro->macros. Once there anyone can see all the macros being used in the workbook.
Is there anyway to prevent the user from viewing the macros, actually I don't mind the names being displayed, but I don't want some of the macros to be launchable on demand by the user.
I have some macros stored that have the passwords to unlock the worksheets. I suppose
I can restructure the macros to possibly avoid this security issue if nothing else is available.
For a table like the one below produced for the sake of example (actual is much much bigger) I want to make it list rows that are true for a certain column for a certain variable in the matrix. So for say water terrain, which types of activity can I do i.e. swimming. Or for Offroad the activites which I can't do i.e. Run and Swim.
ActivityWaterRoadOffroad
Jog nym
Run nyn
Walk nyy
Swim ynn
y=yes
n=no
m=maybe
Is there a way of a user can add a number in a cell and this would hide a different amount of columns.
OR :
Ideally I would prefer a drop down with dates and the user could select a forward date and all the columns with dates up to that would appear. This would mean they could look as far our as required and all unwanted data would be hidden.
I see this forum and the example works perfectly in Excel 2003.. But we are using Excel 2007 now and it doesn't appear to be recognizing the user and is always refering to the "else" in the vb codes. Does this code for not work in 2007? Oh and I am refering to Post #10 on there with the attachment that will hide different sheets depending on which user is getting on the file.
View 3 Replies View RelatedI'm trying to create a macro that allows users to hide or unhide selected rows, either via toggle or userform. The macro I have looks like this, but for some reason it doesn't hide the rows:
Sub Hide_Range()
Dim UserRange As Range
DefaultRange = Selection.Address
Set UserRange = Application.InputBox _
(Prompt:="Select Range to Hide:", _
Title:="Hide Range", _
Default:=DefaultRange, _
Type:=8)
Rows.Select
Selection.EntireRow.Hidden = True
End Sub
what's wrong with my code and how I can further develop it to let the user decide whether to hide or unhide the selected rows?
I have nearly finished my project, but need to make the last real bit of code, then just some general tidying up.
What I want to do
- I currently have two tabs in a userform - one called 'main' and one called 'extra'.
- I want Main to always be shown to the user.
- I want Extra to be hidden (is it possible to make the tabs invisible?) and for this form to be shown only under a specific condition
I have a question on my userform (using Option Buttons):
"Did the customer ask about an extra product today?"
If the answer to this question is no - When the user hits the command button (after filling out the rest of the form), do nothing special - just return all the values to the worksheet
If the answer is yes - When the user hits the command button, I want them to be taken to the 'extra' tab. They will fill out some additional check-boxes, then hit another command box, which will return the info from both the 'main' and 'extra' sheets into the same row of the worksheet.
In brief: I want my 'extra' tab to be hidden, only to appear when 'yes' is selected, and for the results all to go back to the worksheet.