Seeing Cell Comments On Sheet With Hidden Rows
Jan 3, 2010
I have a spreadsheet where i have security set to prevent a user from selecting locked cells. However when a user selects a cell that is in a row at the bottom of the vissible area the comment text can not bee seen. What i need is to determine what the bottom vissible row is and use that to compare the row number of the current row and thus decide if i should scroll down to improve the visibility of the help text for the cell in question.
View 3 Replies
ADVERTISEMENT
Aug 10, 2008
I have a workbook with a hidden sheet ("Template") and a visible sheet("New Job"). I need code so when cell F1 in "New Job" is populated:
1-the sheet is renamed to the value of F1,
2-a new tab is made (a carbon copy of the hidden sheet "Template")
3-the new tab is named "New Tab" and marked as unhidden.
Public Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Name = Range("F1").Value
End Sub
Function WorksheetExists(SheetName As String, _
Optional WhichBook As Workbook) As Boolean
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)
End Function..................
View 2 Replies
View Related
Apr 14, 2007
1. find the last cell used in a column when the rows that contain the data are hidden. I tried using Range("A65536").End(xlup).Select. but when the rows are hidden it doesn't give me the last cell used.
2. I am trying to restrict my search to just one row. When I try using the cells.find function it looks for the whole sheet but I just want it to search for some particular data in just one row or a specified range.
View 2 Replies
View Related
Aug 5, 2008
I have a Workbook with multiple worksheets. On one of my worksheets (Sheet4) I have three cells, let's call call them A1, A2 and A3.
A1 = Annual Budget
A2 = Money carried over from last year
A3 = Current financial year.
Formulas in other sheets are dependent upon these cells. I also have 3 userforms, one relating to each cell, with each form containing a textbox which enters its contents into its respective cell after clicking a command button. The userforms should display if the cell it relates to is empty. For example,
Private Sub Worksheet_Calculate()
If .Range("A1) = "", Then
Budget_Form.show
End If
End Sub
The problem I have is that the userforms only appear if I manually change the contents of the cells that they relate to. I need the sub to run every time the workbook recalculates. On top of that, I want the sheet to be hidden but the subs to still run. After looking around on other sites, I have pieced together the following code and placed it in the code for Sheet4
Private Sub Worksheet_Calculate()..................
View 3 Replies
View Related
Dec 11, 2006
This is my input for password prompt:
Sub CommandButton1_Click()
Dim wSheet As Worksheet
On Error GoTo ErrHandler
Currently, my excel is protected with a password "PSWD" when users open the file. In my vba script, I have a function that can unprotect my excel without any password input box to the users. This method is to unprotect the file and allow users to do any formating columns and rows tasks, such as add or delete cells. After users finish their job, the file will be protected back. The function is such as below:
Dim wSheet As Worksheet
For Each wSheet In Worksheets
Application.ScreenUpdating = False
If wSheet.ProtectContents = True Then
wSheet.Unprotect Password:="PSWD"
Else
My questions are:
1. How I can create a hidden sheet that has one cell populated only?
2. How can I assign the password that the user enters at the "TextBox1" (first code) to a cell of of the hidden sheet? Do I have to modify the first code?
3. For the second code, how am I going to assign the value to at the wSheet.Unprotect/Protect Password:="..." with whatever the user has previously chosen as a password that refers at the hidden sheet?
View 9 Replies
View Related
Aug 14, 2009
With the help of Venkat1926 I established a macro that successfully hides a number of rows that are unrquired (based on dates):
View 14 Replies
View Related
Feb 15, 2007
Is any way to add automatically comments in VBA
Some combination of "ctrl" with something else.
View 3 Replies
View Related
Sep 25, 2012
How do I delete filtered rows without deleting the hidden rows in excel 2010?
View 8 Replies
View Related
Mar 4, 2014
I have an excel file with a table in it. It contains 2051 records (attached). This is just a sample, the original file has around 30,000 rows.
When I start using filters, I run into problems:
Step 1: Filter by Unit, condition (e.g.) Unit_23
Excel shows in the status bar the following message: 437 of 2050 records found.
Step 2: If I scroll to the bottom of the table, the row numbers are colored in blue (normal for a filtered list) however the last row is not colored and it actually should not be shown since its unit is not what I filtered for (its unit is Unit_25)
Step 3: Clear the filter of Unit
Step 4: Sometimes (depending on what I filter for), one or more of the bottom rows are hidden!!!
Attachment 301726
View 4 Replies
View Related
Apr 18, 2013
I have data in B4:B55 and need a formula to return a count of rows, including rows that are blank. However, there are hidden rows that need to be omitted from the count.
View 1 Replies
View Related
Sep 29, 2009
Unfortunately my strengths do not lie in VBA for Excel. But I have a problem which needs resolving using a macro because this spreadsheet scenario happens often.
I have three columns (Record ID, Comment, Results). The Results column is added to capture concatenation of the Comments column. Using VBA, I want to concatenate Comments for each Record ID in the Results column on the row for that Record Id where the rows above have no Record Id.
Each comment should stack in order with a carriage return between them. This should happen for each row where a Record ID exists and continue for the length of the spreadsheet until it finds the last row with a Record ID and should stop after concatenating its results.
View 8 Replies
View Related
Nov 2, 2009
I have been provided a spreadsheet which has a Quantity column "H". In this column is the quantity of the items and a note containing the serial numbers of the items. The note has no real format. Here are some examples:
Example 1: Serial numbers: 161702, 161722, 161740, 161741.
Example 2: Dec 2006: s/n 63194
Jan 2007: shipped 1 widget, 52480
Example 3: Sent in 2004: 717, 718
Apr 07: shipped 63522 and 63524.
Oct 30, 2007: K. Grande reported only two widgets onboard, 63522 and 63524. 717 and 718 are missing.
Nov 2007: 160302.
Dec 14, 2007: shipped 161093.
There are thousands of serial numbers on hundreds of rows like this. I am looking for code that would copy a new row to another sheet for each serial number in the comment with the serial number placed in column "J". I don't know if the extraneous text can be filtered out, but if I can at least get everything on a seperate row, I can sort/delete what I don't need a lot faster than doing this manually.
In have uploaded an example with before and after sheets. The After sheet is what I have done manually so does not match the examples I used on the Before sheet.
View 6 Replies
View Related
Dec 6, 2009
I would like to use a macro to automatically extract the comments in Sheet 1 ( those comments are Customer Names and Their addresses ) to a new Sheet 2 which will print shipping labels.
I have manually created the first three labels on Sheet 2 to show what I want to accomplish. The tricky thing is, that some Customers receive 2 or more artworks...
View 11 Replies
View Related
Jul 28, 2008
I have a single workbook with 4 worksheets in it. This is a dimensional report sheet. We measure stuff and report our findings.
The first worksheet is the master sheet. The next three sheets are filled in by three different inspectors.
I've got a formula in all the appropriate cells in the master sheet that brings in values from the other 3 sheets. It uses an IF formula. If there is no value reported on any of the 3 inspection sheets, the master sheet displays the text "MISSING".
So, the Master Sheet is the compilation or summary sheet, if you will.
Is there a way to have the master sheet bring in not only the values, but also the comments of cells from the other 3 sheets? There will be no cell that has comments on more than one inspector sheet.
Right now, I'm using Copy -> Paste Special -> Comments. I'm looking to automate this, so that as soon as an inspector fills in a value and adds a comment, both items get put into the master sheet automatically.
View 9 Replies
View Related
Mar 14, 2014
I have this code that first selects a sheet (data) and then imports a csv-fil to the data sheet.
I want to hide the data sheet and the the code doesn't work.
How do I change the import code so that I do not select the sheet?
[Code] ......
I believe it should be something like this: With Sheets("DATA").QueryTables. Add..... but I am not aware of the syntax.
View 1 Replies
View Related
Dec 12, 2013
I have a userform which clones the latest sheet and produces copy of it on the next sheet. The first sheet they will be cloning is the sheet called 'template', I however want this to be hidden since I do not want anybody to modify a sheet which basically serves purpose of a template. And once cloned, it is no longer needed.
On the click of the command button, the macro will create sheet1 taking the info from sheet named 'Template' in the same workbook . And now on the next click of the command button, Sheet 2 is created taking the info from sheet1 and Sheet 3 is created taking the info from Sheet 2 and so on. Here is what I currently have, so how can i modify it in order for my scnerio to work?
To select the last sheet in the workbook
Code:
Sheets(Sheets.Count).Select
To create new sheet
Code:
Sheets(Sheets.Count).Copy After:=Sheets(Sheets.Count)Sheets(Sheets.Count).Name = MyEvent & " " & MySCN & "(" & ThisWorkbook.Sheets.Count - 2 & ")"
View 3 Replies
View Related
Mar 2, 2007
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'.
Sub UnhideSheet1()
Sheet2.Visible = True
End Sub
View 7 Replies
View Related
May 29, 2014
I am trying to use the SUMIF function to create a formula that will read the visible cells in Column B and if any visible cells in Column B are equal to or greater than zero I want Excel to sum the corresponding values in the visible cells of Column A. Not so hard but I'm having a difficult time getting Excel to ignore data in hidden rows.
See attached file for and further explanation : Sample.xlsx
I've tried using SUMIF as well as the AGGREGATE function but no success, yet.
View 5 Replies
View Related
Jan 24, 2014
I have a spread sheet which has a number of protected cells so that they user can tab only on required cells to enter data. At times some of the rows are hidden however pressing "TAB" will continue to tab through on the cells within the Hidden Rows. Is it possible to only TAB through Cells which are visible?
View 1 Replies
View Related
Sep 5, 2009
I have a worksheet with a macro as follows: ...
View 6 Replies
View Related
Nov 19, 2006
I need a formula for counting rows. It should achieve the following;
It should count in increments of 1 (1,2,3,4, etc.) in each cell in a column (column AW, to be prescise).
It should skip hidden rows.
It should account for the fact that a formula is able to reveal rows and when this is
done, the counting formula should adjust to count the newly revealed row.
It should also be able to do the opposite - another formula/macro hides rows, and when this happens it should not count the newly hidden row.
I can imagine a formula in each cell of the column that says "Check the previous column and if it is visible, add 1. If a hidden row is encountered, do not add 1. When a non-hidden row is encountered again, continue adding 1."
View 9 Replies
View Related
Dec 15, 2009
I have a worksheet with hidden rows and Page Breaks. I know that the page breaks are causing my worksheet to print blank pages where I have page breaks and hidden rows.
I need the page breaks or the form just prints a mess... I tried eliminating them - but that didn't work at all.
I need a macro, which will examine the worksheet, look for the hidden rows, exclude them from the print area, and then Print the worksheet.
View 9 Replies
View Related
May 6, 2006
Why do I get a "sub or function not defined" error on the first line? The problem seemed to arise out of nowhere. The sub is located in module 1. I call it with "Call DeleteHiddenRows" in sheet1 inside of a "Private Sub Worksheet_SelectionChange(ByVal Target As Range)" event. Should I be adding some declaration somewhere (some "dim" line?)???
Sub DeleteHiddenRows()
For j = ActiveCell.SpecialCells(xlLastCell).Row To 1 Step -1
If Rows(j).Hidden Then
Rows(j).Hidden = False
Row(j).Activate
Selection.Delete
End If
Next j
End Sub
View 4 Replies
View Related
Jan 18, 2007
I'm completely at a loss I have a worksheet with hidden rows which I do not want to print yet Excel prints them anyway resulting in 12 pages rather than just the pages I want.
View 7 Replies
View Related
Mar 21, 2007
i count number of all rows with:
Dim countall As Long
countall = Sheet1. Range("a1", Sheet1.Range("A65535").End(xlUp)).Rows.count
this is the total of all rows, including hidden. how do i count the hidden rows which have been filtered?
preferably not a loop, isn't there a way to use "entirerow.hidden" and count that?
View 5 Replies
View Related
Jun 7, 2007
I am trying to create a macro to unhide only a row at a time, from around 150 hidden rows altogether. It is basically to give the effect of adding extra rows to a "table" that a user could then input with new data (I have to do this way according to other set-up in the workbook), but this isn't very important to do with this question. When I do a Format<Row<Unhide from the Excel menu, it always unhides ALL of the hidden rows. I have fiddled around but can't seem to find a way to avoid this happening, all I want is for just one row to unhide. I thought it might depend what cell/s I had highlighted, but I haven't found a way that makes it work.
View 7 Replies
View Related
Aug 21, 2007
I HID 4 rows using this help section. (Format - Hide)
Then, I modified several column widths simply by placing my curser at the top of the column and pulling it smaller or larger.
(I don't know if this did or did not cause my problem) which is simply that - I am now unable to find my hidden rows.
I know where they are supposed to be, but when I highlight them and click (Format - Unhide) nothing happens.
I tried (Find and Select - go to special) and no white bar shows up.
Can sombody please tell this frustrated Newby How to find the rows I just hid 1 hour ago?
If I posted in the wrong place, I appologise as I spent more time trying to figure out where and how to post than I did trying to solve my hidden row problem.
View 5 Replies
View Related
Sep 10, 2007
Is there a way to keep the Sum Funtion from adding in the values from Rows you have hidden? I want my total to be the result of only the visible lines, but have reasons for hiding rather than deleting rows (I may have to unhide some later depending on other factors)
View 9 Replies
View Related
Mar 27, 2008
i have a workbook that has a lot of sheets but i need to pull information from the one sheet "Veneer Log" i Need it to make new sheets with the same heading as on the "Veneer Log" (Rows 1 & 2) Sheet but it needs to be filtered by the "Product" Column (H) with a new sheet made for all the diffrent products i.e. Dimensional, Drywall, Corners - Thin V., Accents,..... so each product will have a new sheet with i am hoping someone can help me with this. This log changes Daily and it would be nice to have a sheet with only the same product on it to compare new orders so we can batch run. i hope i have given you enough information so someone can help me with this. i have attached a sample log the real log has about 10 worksheet for diffrent departments but i only need info from the Veneer Log Sheet.
View 14 Replies
View Related
Aug 16, 2013
I'm wondering if it is at all possible to have a cell comment automatically update depending on what data is input in the cell (via user input, VLOOKUP, etc.)?
For example, if cell A1 contains the text "CHARLES" with a comment saying "Employee of the Month", and is then subsequently updated with the new text "JOHN" (again, via direct input, a VLOOKUP, data validation, etc.) is there a way to have the comment automatically update to say something else, such as "Team Lead" for example?
I've considered using VBA to accomplish my goal, but am unsure how to compose an effective code to do so. I've also considered perhaps creating a named table filled with all the different comment possibilities I would like to have used in this cell and then inputting a formula in either cell A1, or the comment contained therein, that would then call the corresponding text from that table based on the data in cell A1. Honestly, I'm not sure that what I'm trying to do is even possible;
View 4 Replies
View Related
