Posts by Blue_Hornet

Here in OzGrid I've learned about programming Worksheet_Activate events--or at least I'm 'starting' to learn. Obviously I have a ways to go...

I thought it was nice to have 'beginning' and 'end' sheets that wrap on Ctrl-PgDn or Ctrl-PgUp to continue back at the 'first' sheet (if scrolling to the 'end' worksheet) or return to the 'last' sheet (when scrolling to the 'beginning' sheet), respectively.

So, for a simplified example, I have 5 sheets, with CodeName "Sheet1" through "Sheet5". Only 3 of the sheets have actual data, Sheet2, Sheet3 and Sheet4.

In Sheet1 'Worksheet_Activate" code I have entered:
Private Sub Worksheet_Activate()
    Sheet4.Activate
End Sub

And on Sheet5 I use:

Private Sub Worksheet_Activate()
    Sheet2.Activate
End Sub

And this works. As I scroll Ctrl-PgDn from 2 to 3 and 4, then run past 4 to 5, I'm immediately put back to Sheet2, my first 'Data' sheet. Conversely, if I scroll Ctrl-PgUp from 4, to 3, to 2 and then to 1, I'm immediately set back to #4 again.

This looping works great both ways as far as I care to run it. So far, so good.

But then I thought I'd get just the least little bit cooler and always have the Workbook open to Sheet2, no matter where it was saved. So the coding seemed simple:

Private Sub Workbook_Open()
    Sheet2.Activate
End Sub

But it doesn't work. It keeps sending me to Sheet4 instead, no matter what I do. (Even when I save the workbook at Sheet2, it now opens up with Sheet4 active!) It's as if the code has somehow actually activated Sheet1 instead, which is the looping 'back' to Sheet4.

This isn't so important, because I can always take that Workbook_Open coding back out, and I've still got a 'looping' feature that I like, but I'm puzzled. What's going on?

Thanks.

Start Excel
(Assuming the Personal.xls spreadsheet is visible)
Windows / Hide
Quit Excel
Answer the prompt "Do you want to save the changes you made to Personal.xls?" ... YES

It should still open every time you start Excel (that's what you want it to do), but it'll be hidden.

Chris

This might work for you as the formula to be entered in the cells:

Selection.Formula = "=IF(OR(not(isblank(DataSheet!B" & tc & "))," _
& "not(isblank(DataSheet!B" & tc + 1 & ")))" _
& ",MAX(DataSheet!B" & tc & ":B" & tc + 1 & ")," & """ & """ & ")"

I try to stay away from the "" entry in inputting text, because I ALWAYS forget how many double-quotes to use. That's why I substituted the 'not(isblank(cell reference))' construct in the conditional formula.

I also changed from 'Selection.VALUE' to 'Selection.FORMULA', though I don't know if that made a big difference or not.

Chris

Thanks, Richie (and Chip). Just what I wanted.

Chris

Thomach:

I guess I wasn't clear enough in my question. I'm trying to populate cells in the sheet with some of the data from File / Properties, such as 'Title', 'Subject', 'Author', 'Manager', 'Company', etc. which are NOT available (at least as far as the HelpText you posted) with
=CELL( info_type, reference)

(The corresponding Word functionality is:
/Insert
/ Field DOCPROPERTY "Name", where "Name" is one of the named fields above. )

Better?

Thanks again,
Chris

In Excel '97, where can I find some of the file properties to include in worksheet cells, such as "Revision date" (= last modification), "Author" (= created by), and other such data?

Can I do it with a standard worksheet formula? (I'm familiar with =cell("filename") and some other 'information' fields, but not many of the ones I'm after.) Or will this be a custom function of some sort?

Thanks,
Chris

Hi, Laura

See the example I've posted to show how you can use the OFFSET function and a key field (whatever you want it to be) to count down a certain number of rows to find the 'final balance' you want.

Chris

This is a long post, but pretty simple, really. It's the problem itself that's annoying and frustrating.

I'm getting 'Macro Virus Warning' messages from Word when I KNOW that there is no macro or module contained in the file. (I can create a .doc file that causes the Macro Virus Warning to display upon opening the file, but I can't figure out how to take that out again. In other words, I don't know 'how' I am doing this.) I prefer to keep the 'Warning' option turned on, because I do open docs from colleagues, and I do want to know when macros may be included.
Here is what I have done to create a .doc file that displays the warning message (done in Word):

Type a new document:

"This is a test document to demonstrate ‘false’ macro warnings. It is first created by ‘File / New / Blank Document’, this text is typed, and it is saved as ‘TEST.DOC’." (Then do all of that.)

Since there is no macro code at all, and nothing has been done to spoof a macro, the file can be saved and re-opened at will with no problems. (Tools / Options / General / ‘Macro Virus Protection’ is ‘ON’ and should remain 'ON')

Now run VBA (Alt-F11) and view the Code window if it isn’t already opened. Before doing any more to the document, go back to the Word document and save it again (to assure that simply opening VBA hasn’t corrupted it).

Re-open the document and re-run VBA to see the Code window again. In the Code window click the top left drop-down (‘(General)’ by default) to get the ‘Document’ region. This enters tags for ‘Private Sub Document_New ()’ and ‘End Sub’, but no actual code.

If anything is entered between those tags and saved (even as little as a space character), then Word seems to recognize that 'a macro exists'. When the document is then saved and retrieved the macro warning is activated. Fair enough. But trying to delete the macro that has been so created has not been successful. There seems to be nothing I can do any more to wipe out the false ‘macro’ and avoid the warning dialog on opening the file.

How can this entire process be reversed to completely eliminate the phantom ‘macro’ that is created?

Thanks,
Chris

First create a macro in a standard module, that we can call: Sub GoToRow()

Option Explicit
Dim myRowNum As Integer
' To go to Range("D" & VaryingRow),
' where the row number varies by day, to equal the day of the month.

Sub GoToRow()
myRowNum = Day(Now())
Range("D" & myRowNum).Select
End Sub

To have this operate on a sheet-by-sheet basis, right-click any sheet and 'View Code'. When you have the code window open change the default Sub name to: Sub Worksheet_Activate(), and use the single line of code "GoToRow".

You'll have to do this on each sheet that you want this to operate on.

Chris

Dennis:

I like that; I like it a lot! It was something I only 'sorta' knew that I needed. I took the liberty of modifying your code slightly to show all of the names at once, rather than 'by iteration':

Option Explicit

Sub Modified_Check_Namerange()
Dim wbBook As Workbook
Dim rnName As Range
Dim nName As Name
Dim nNameCollection As String

Set wbBook = ThisWorkbook
nNameCollection = ""

'Here we loop through the workbook namecollection.
For Each nName In wbBook.Names
On Error Resume Next
Set rnName = Nothing
Set rnName = nName.RefersToRange

If Not rnName Is Nothing Then
If Not Application.Intersect(rnName, ActiveCell) Is Nothing Then
If Not nNameCollection = "" Then
nNameCollection = nNameCollection & ", " & Chr(10) & rnName.Name.Name
Else
nNameCollection = rnName.Name.Name
End If
End If
End If
Next nName

MsgBox "The active cell is within the following rangename/s:" & Chr(10) & nNameCollection
End Sub

One thing I've noticed is that if two ranges are identical, the macro returns the first name for each time 'that range' comes up. But hey, you could call that a feature! (Why would anyone want two identical ranges with different names, anyway?)

Thanks!
Chris

Not to be too awful persnickety, but I got stumped trying to work on a macro that would run down the column of numbers only, examine for format and modify the format according to what was already there. Anyone want to give that a go? (It's the more direct approach, and will solve the problem when the text in a neighboring cell isn't quite what is expected, etc.)

Chris

I just realized that I may not have answered your question, since it appears that you want to list 'other' sheet names in 'this' sheet. It's very simple. Refer to what I showed you above, and just include the other sheet by reference, such as:

=CELL("filename", Sheet1!A1)

Chris

Neo:

Here's a function you want to be familiar with:

=cell("filename") ... and options for using it.

=CELL("filename")
will show your entire PATH, FILE and SHEET name as, for example:
C:Documents and Settingscln01My DocumentsApplication Tips[Some File.xls]VLookup Example (this is the file I have set up as an example)

=LEFT( MyFile, FIND( "[", MyFile) - 2)
will show the PATH only, such as:
C:Documents and Settingscln01My DocumentsApplication Tips [from the same file as above -- where I have a defined name of MyFile = cell("filename")]

=MID( MyFile, FIND( "[", MyFile) +1, FIND( "]", MyFile) -1 - FIND( "[", MyFile))
shows the FILE name only, or
Some File.xls

and

=RIGHT(MyFile, LEN(MyFile) - FIND("]", MyFile))
returns the TAB name only, or
VLookup Example

No, these are not real simple formulas, but I have them grouped in a workbook that I can refer to (that further simplifies things with more Named Ranges), if my attachment works.

Chris

Nicole,

Not sure that I fully understand your problem here, but it looks like the only issue you have is with the display of the numbers, correct? (The numbers in your example are not rounded to the nearest hundredth, that's just the display.) That is, you aren't actually 'doing' something to them to change them, but you want them to display more significant digits.

That's all governed by your cell format. You have numbers (in your example) which could be formatted using VBA as:

Selection.NumberFormat = "0.00%"

and

Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"

If you want to change that, just re-write the 'picture' that you want the numbers to display, such as:

Selection.NumberFormat = "0.00000%"

for example.

Chris

Derk has answered the question you asked, though it doesn't work for me.

Here's something I could get to work, to answer your specific question:

Sub Test_Loop()

Range("Start").Select

StartRow = ActiveCell.Row
StartCol = ActiveCell.Column

For col = StartCol To 100
If Cells(StartRow, col).Value = "End" Then GoTo EndNow:
Cells(StartRow, col).Value = "Okay"
Next col

EndNow:
MsgBox "We're done."
End Sub

but here's something else you might consider, to avoid loops in the first place--and why you should try to do that (from OzGrid):

"Don't get caught in the Loop" on http://www.ozgrid.com/VBA/MiscVBA.htm

(I'm only starting to become dimly aware of this, myself ... lol.)

Chris

Well, the simple answer to your specific question is:

=LARGE(Larry:Curly!$B$3:$B$4,ROWS($B$3:B3))

copied down as many rows as there are entries to consider, which in this case is 4 rows down. That accomplishes 'exactly' what you asked for. (And will keep the values 'sorted' as you modify Larry and Curly, and keep calculation automatic.)

But I wanted a better answer. What if you have multiple sheets and different sized ranges to consider on each sheet? Obviously you'd want a more robust solution.

So I thought about an array of named ranges: Larry_Range, a selection of 5 cells on 'Larry', and Curly_Range, a selection of 10 cells on 'Curly'.

I tried a variation of the same formula:
=Large(Curly_Range:Larry_Range, 1) (etc. and variations on that idea) and got nowhere. Anyone have any ideas for fixing this? Now I'm curious.

So what I settled for, which does work, but maybe not very elegantly right now, is:

In B19 (copied down twelve rows) on Moe I entered:

=IF(ISERROR(LARGE(Larry_Range,ROWS($B$19:B19))),"",LARGE(Larry_Range,ROWS($B$19:B19)))

This returns the 5 values, in descending order, with 7 cells of 'nothing' following.

In C19 (copied down twelve rows) on Moe I entered the corresponding formula for Curly_Range:

=IF(ISERROR(LARGE(Curly_Range,ROWS($C$19:C19))),"",LARGE(Curly_Range,ROWS($C$19:C19)))

And this returned the ten values I expected, plus two cells of 'nothing'.

So this gave me two columns of sorted values from Larry and Curly (and zeros). Then in D19 (copied down twenty-five rows or so):

=LARGE($B$19:$C$30,ROWS($B$19:B19))

This returns the 15 actual entered values, followed by #NUM errors where there is no value to return from the function. (This could be error-trapped, as well, of course, but that's where I gave up for tonight.)

So, I was able to do what YOU wanted, but not as nicely as I would want to do it. There must be a better way.

Hope this helps.

Chris

Thanks, guys. That got it. The other thing I was missing was to enter the Workbook events code in the 'This Workbook' object. (See, sometimes it's the simplest things! I've been so used to 'Insert / Module' and coding there, that I hadn't realized that each sheet and the workbook had their own code 'module-ability' if I may coin that word.)

I got that from the link to Mr. P's site, so I owe him, too. (Dennis, you had said it, but the text on Chip's site took me by the hand through the Objects listing to 'This Workbook' -- I'd never been there before.)

Chris

I've been programming with Excel VBA for some time now, but I'd like to increase my abilities by adding various Event programs (BeforeSave, ActivateSheet, etc.) to my Sheets and Workbooks.

A couple questions to begin:
1. Where can I learn the various Event names and what they all mean (those that aren't self-evident)?

2. The specific question I'm trying to answer right now is: 'How can I set up a macro to modify a named cell "RevDate" and update it to the value of "=Now()" [but not that formula, since I only want the cell to show when the sheet was last saved, and not 'now()' all the time] at the time that the workbook is saved?'

I think if I can get some examples and a start on this, then I can improve my coding a lot, but trying to find a starting point is like trying to climb an ice cliff (and I'm not an ice climber!).

I've seen the very cool OzGrid example for "Stop Users from Scrolling About" [at http://www.ozgrid.com/VBA/MiscVBA.htm], which was where I got started with the idea, but I haven't gotten past that yet.

Thanks,
Chris

Enter into B51:

=if( isblank( b54), 0, 1 - b54)

This formula looks at B54 to see if there is something there (no error-checking for text vs. number--see below). If there is 'nothing' in B54, then '0' is returned (the IF is true).

If there is 'something' in B54 (the IF is false), then enter the value "1 - B54" (or 100% minus whatever % value is in B54).

Here's additional error-checking:
=IF( ISBLANK( B54), 0, IF( NOT( ISNUMBER(B54)), "Enter a NUMBER in B54!", 1 - B54))

Chris