Posts by yjoshi


    I think the use of If is not really required,

    What you can do is.... multiple the two series of true & false and you will get the numbers...


    Instead of


    This will certainly reduce the code, and also use internal features of excel (i.e. TRUE = 1 & False = 0) and their multipliaction...

    I tried it in your sheet and it worked well.

    What do you think???

    Interesting question ...

    I will do it this way, instead of changing it in page, I change the value in a cell, say E1

    and then change all the page values.... by this code....

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Union(Target, Range("E1")).Address = Range("E1").Address Then
    For Each pvt In ActiveSheet.PivotTables
            pvt.PivotFields("City").CurrentPage = Range("E1").Value
    End If
    End Sub

    It is working for me... .let me know if it does not.... I will attach a file.


    Your query was not irrelevant :)

    Put this code so that when you start the form for the first time, even if the caption is
    something different, it will be set right.

    Private Sub UserForm_Initialize()
        CommandButton1.Caption = "Start"
    End Sub

    Following code works with MS Outlook.

    Hope this helps ... Interesting requirement BTW ...

    Also if A1 and A2 are calculated fields, please put proper range you want to compare, or if you want to alert / send mail every time there is a change, you may choose to remove the criteria.


    You can do it in two ways. Simple formula and array formula.

    If you want a simple formula, : enter following in any cell other than A1 to A9

    What it does is....
    1. Takes values in A1 to A9 and makes it CAPITALIZED
    2. Takes first 3 chars and compates with APPS
    3. if this is true, it generates an array of TRUE & FALSE
    4. TRUE = 1 and FALSE = 0
    5. when you multiply it by one, it creates series of 1s and 0s
    6. when you do a sumproduct, it totals all 1s and gives you the result.

    IF you want to use Array Formula for this : use following

    Note that, you have to enter =SUM((LEFT(UPPER(A1:A9),3)="APP")*1) and press
    "Ctrl + Shift +Enter" to get the { } s and not enter manually.

    You can see following links for getting more info on WHAT CAN BE ACHIEVED WITH ARRAY FORMULAE :: enjoy

    Also see array formulas link on OZGRID.

    I have done this ample time, but never faced an issue.

    Can you try this?

    Create a new file, put many controls on that page then try to print prieview....

    If the problem persists, then this can be a BUG with Excel 2002 ;)
    ... otherwise looks like a file corruption.

    Please provide an update..

    Hi Admin, (Quite sure this is not Dave's Update)

    The issue is, Daniel wants to have the list and also avoid duplicates.
    So just avoiding duplicates does not solve the issue.

    Just a clarification :)

    Here is how you can start....

    This is not EXACTLY what you expect, but can help you.
    1. Create a List of numbers a person can use
    2. Besides the list create another list, which will exclude those items which
    are already selected.
    You can do this by formula :
    Where, A1 is from the original list, Sheet1!A:A is the range where you want
    to have the validation, i.e. entry row....
    what it means is, if this number is already used, it shows "" otherwise the
    3. Create a named range for this new range, say unused_nums
    4. Now create the validation with this named range.

    You will see blanks in place of numbers that are already used.

    I know this is not the best solution, if you have many numbers that can be blank but it does work.


    I will suggest a simpler solution

    Just use one button with Caption "Start" and in the run time change it
    to "Stop" once it is clicked.

    If the caption is Start when clicked, it will enter date and time in Cols A & B respectively
    If the caption is Stop when clicked, it will enter date and time in Cols C & D respectively, in the same row it started, thats why i have used (1,1) in first part and (0,3) in second part

    Let me know if this does not solve your problem

    Hi Will,

    You can access properties of a particular condition using

    ? ActiveCell.FormatConditions.Item(1).Interior.ColorIndex

    But the question that WHICH item is currently activated is still not known...

    Looks to be an interesting problem, but I think this can be a good starting point

    I will suggest following code

    Private Sub CommandButton1_Click()
    Range("A65536").End(xlUp).Offset(1, 0).Value = Now
    End Sub
    Private Sub CommandButton2_Click()
    Range("A65536").End(xlUp).Offset(0, 1).Value = Now
    End Sub

    The Reason for replacing now with Date & time is that its combination
    of both and you can take out all the information you need with this.

    Following code can help you to get a complete list of all the links on a particular sheets shown on a new sheet
    You can then go through the list and determine if there is any unwanted link.

    Here goes the code

    Sub page_of()
    Dim sht As Worksheet
    For Each sht In Sheets
        sht.Range("A20").Value = "Page " & sht.Index & " of " & Sheets.Count
    End Sub

    If you want this for printing purpose... i will suggest another way to do this.
    On each sheet setup appropriate header or footer (Page # of #). then select all the sheets you want to print and see print priview. You will see that appropriate page numbers are set and works fine even if one sheet has more than one pages.

    Hope this Helps

    Do you want Paste Transpose?

    Sub CopyTest1()
       Range("A65536").End(xlUp).Offset(7, 0).PasteSpecial Transpose:=True
    End Sub

    My sincere apologies if i am still unable to understand.... If you can describe your problem better, it can help