R
Ron Rosenfeld
A FUNCTION cannot affect cell attributes.
A FUNCTION can only return a value.
So there is no way that a FUNCTION can change row height.
--ron
A FUNCTION cannot affect cell attributes.
A FUNCTION can only return a value.
So there is no way that a FUNCTION can change row height.
--ron
D
Dennis Allen
Ah. So how do we tie into the OutofOrder sheet? If someone adds or removes a "Y" log entry row, it needs to show up on the
OutofOrder sheet.
OutofOrder sheet.
R
Ron Rosenfeld
I still don't understand how you can ensure you have only one "out of order"
entry marked "y" on your 400 sheets. How do you ensure that someone deletes a
previous out of order entry before entering a new one?
In any event, as I wrote some postings ago, and without understanding
everything, it seems you need to use a SUB procedure. Whether this should be
activated by a Button, or automatically -- perhaps with a Worksheet_Activate
event, is something you have to decide.
Again, I'm still not sure of how things should be set up. But play around with
this idea:
On your OutOfOrder worksheet, I guess you will have one line for each component
worksheet. These are the sheets you have named 10001, 10002, etc.
In addition, you have references to each worksheet that are from someplace at
the top section of each sheet, so do not depend on searching the worksheet.
By the way, I do not understand why you go through the bother of hiding the
columns that have the INDIRECT function in them, and testing the presence or
absence of data in a separate column. You could do all that in the same
column. For example, in J4 you have: =INDIRECT("'" & $B4 & "'!B5") and then in
K4 you have =IF(J4>0,J4,""). Why not combine them in J4?
=IF(INDIRECT("'" & $B4 & "'!B5")>0,INDIRECT("'" & $B4 & "'!B5"),"")
Also, if all of your sheet names are without spaces, then you don't need the
apostrophes. So you could use =INDIRECT($B4 & "!F4"), as an example.
Looking at your sample workbook, it seems to me the simplest, and most
automated method would be to use the worksheet activate event on the OutOfOrder
worksheet.
First of all, given the current structure of that worksheet, just change the
code in Q4 to read =P4. You don't need that UDF that you have in there.
Right click on the OutOfOrder tab and select View Code. In the window that
opens, paste the following code:
====================
Private Sub Worksheet_Activate()
[P3].AutoFilter field:=16, Criteria1:="y"
End Sub
===================
What that does is run the autofilter looking for a "y" in field 16 which is
column P. It does that whenever you activate that worksheet.
If you reduce the number of columns as I suggested, you will need to alter the
SUB to select the proper field.
--ron
R
Ron Rosenfeld
Dennis,
Here is another SUB that seems to work as well. It has the advantage of
perhaps looking a bit "neater" than the autofilter as you do not see the
dropdown lists (down arrows). However, it may take longer to run with 400
lines.
(It is possible to remove the drop down lists on the autofilter command, but
that seems to take a long time, so I do not suggest it).
Enter it as "worksheet code" following the same procedure as I described for
the above.
===========================
Private Sub Worksheet_Activate()
Dim c As Range
Application.ScreenUpdating = False
Cells.Rows.AutoFit
For Each c In [p4
403]If Not c.Text = "y" Then c.RowHeight = 0
Next c
Application.ScreenUpdating = True
End Sub
============================
You may need to change the range p4
403 if you move around the "y"--ron
D
Dennis Allen
Ron Rosenfeld said:
The last log entry indicates if the item is out of order. LastST() checks the date column for the latest log date, then checks for
a "y" on that row. So when a sheet is no longer out of order, the user has to either remove the "y" from that last entry or make a
new entry.
If you combine them, how could you access their value in other formulas? Add this to D4:
=IF(INDIRECT($B4 & "!B4")>0,INDIRECT($B4 & "!B4"),"")
Noticed what happened to I4? Doesn't work anymore. That's why hidden cells like J4 have values I can use in other formulas while
display cells like K4 remove those pecky "1/0/1900" and "0" (unless you have a better way). I wish I could do that on the
individual sheets for cells like B4:B6, D4
6, F4:F6.Oh. Didn't know that.
Well, =IF(P4>0,P4,"") to remove that pecky "0".
Tried it. Works! Thanks. Noticed it doesn't matter if the user hits "y" or "Y". I like it.
Something very wrong. I was playing with the temp.xls. Added [P3].AutoFilter field:=16, Criteria1:="y" to the Worksheet_Activate()
of OutofOrder. After the sheet recalculated, all the UDFs everywhere went #VALUE! What happened? If you put in a new entry,
causing a UDF to run, the #VALUE! goes away for that cell. But only that cell.
I tried it with the full copy. After adding the OutofOrder sheet, and the SUB to the OutofOrder sheet, after recalc I again get
#VALUE! everywhere. I didn't get it before, why now? I don't want to send this off to the client until I know what's causing it
and how to fix...Dennis
D
Dennis Allen
Ron Rosenfeld said:
Dennis,
Here is another SUB that seems to work as well. It has the advantage of
perhaps looking a bit "neater" than the autofilter as you do not see the
dropdown lists (down arrows). However, it may take longer to run with 400
lines.
(It is possible to remove the drop down lists on the autofilter command, but
that seems to take a long time, so I do not suggest it).
Enter it as "worksheet code" following the same procedure as I described for
the above.
===========================
Private Sub Worksheet_Activate()
Dim c As Range
Application.ScreenUpdating = False
Cells.Rows.AutoFit
For Each c In [p4
If Not c.Text = "y" Then c.RowHeight = 0
Next c
Application.ScreenUpdating = True
End Sub
============================
You may need to change the range p4
I like it! Avoids the autofilter problem altogether. I still can't figure out how we got the autofilter SUB to work the first time
or why I can't get it to work this time.
R
Ron Rosenfeld
If you combine them, how could you access their value in other formulas? Add this to D4:
=IF(INDIRECT($B4 & "!B4")>0,INDIRECT($B4 & "!B4"),"")
Noticed what happened to I4? Doesn't work anymore. That's why hidden cells like J4 have values I can use in other formulas while
display cells like K4 remove those pecky "1/0/1900" and "0" (unless you have a better way). I wish I could do that on the
individual sheets for cells like B4:B6, D4
You can remove the 'pecky "1/0/1900" and "0"' by formatting the cells to not
display 0's. That way you don't need the extra columns. So instead of
combining the formulas, in D4, leave the formula as you have it:
=INDIRECT($B4 & "!B4")
and change the format
Format/Cells/Number Custom Type: m/d/yyyy;;
Note the two semicolons at the end. That tells Excel if the value is 0 (or
negative) don't display anything. But the 0 is still available to use for
calculations.
I'm glad this is working for you now. Although I may check in tomorrow
morning, I'll be gone until Sunday PM.
So if you have more questions, there'll be some delay in my response.
No time to look into the issue with autofilter, but since hiding the rows is
working, there's no need.
Best,
--ron
D
Dennis Allen
Ah. I'll try it. Thanks for all your help...DennisRon Rosenfeld said: