Posts by Insomniac

    Hi all,

    I have 2 computers, one running Windows 98SE and one running Windows ME.

    When you you type '=' in a cell and click on dropdown arrow for formulas you should see the most recently used formulas and at the bottom 'More Functions'.

    On my 98SE comp this is true and all other comps I have acces to also. But on my ME comp I do not have the last option?

    Any ideas?

    Ok I see your point, I was under the impression that it was only reqiured to highlight the row with the absolute maximum value in row L.

    This will only work if the values for each agent are in a separate column.

    I stand corrected!!!!!

    I tested it and it will format any column with this conditional formatting.

    The $L in the formula means it will check the condition of the cell in row L no matter what column the formatting is in.

    Will post a sample if you want!

    Hi AJW again,

    Here is a small demonstration workbook, had to find time to throw it together.

    Download and save in a new folder

    Read the NOTE sheet

    Use any of it that you find of value!

    (There is code in the Workbook and sheet DATA as well as module1)

    Regards Insomniac

    You could place the UDF in Personnal.xls as long as it is open it will be available to all Workbooks.

    Make sure it is a Public Function
    not Private Function.

    Dont have Option Private Module in declarations.

    I save as an add-in (.xla) for this, you can save the workbook anywhere just place a shortcut to it in your XLStart directory and it will open with Excel.

    If you want stand alone workbooks to give to others its probably best to copy it to those workbooks.

    Yes you only need add the cell value to the path.

    ActiveSheet.Pictures.Insert ("C:\PlatMaps\" & Range("A1"))

    Where cell "A1" contains a valid picture name ie MyPic.bmp
    or MyPic.jpg

    Create a new Workbook

    Add a CommandButton with ControlToolBox
    ToolBar (CommandButton1)

    Right Click on Sheet Tab

    Paste this code

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    CommandButton1.Top = ActiveCell.Top
    CommandButton1.Left = ActiveCell.Offset(0, 1).Left
    End Sub

    Return to Excel Worksheet and scroll around the screen and the button will follow.

    This is very crude and will get error if you go to column IV but should satisfy your query

    I think this is what you are seeking.

    I have used an array formula


    I have copied sheet1 to sheet3 of your sample Workbook and inserted the array formulas in columns H and J

    If you are interested I used the Conditional Sum Wizard to work out the formula.

    Regards Insomniac

    Hi Ranger,

    try this if a macro is OK with you

    Sub CountHighlightNames()

    Dim Name As String, Rw As Integer, Count As Integer

    Count = 0

    Name = InputBox("Name to search for")

    For Rw = 1 To 20
    Cells(Rw, 1).Interior.ColorIndex = xlNone
    If InStr(1, Cells(Rw, 1), Name, 1) Then
    Count = Count + 1
    Cells(Rw, 1).Interior.ColorIndex = 6
    End If

    MsgBox "Found " & Name & " " & Count & " times"

    End Sub

    The search is not case senesitive.

    ie. jack will still find Jack

    Cells should become yellow if the name is found!

    High again AJW

    Iam not sure if the code I supplied will work as I see that this site has added 'Smiley faces'to some of it!

    Keep in mind that I set up my workbooks with this method in mind, I usually set aside columns A-Z for user entries and as the TextDump Range.(rest of columns may be formulas, links etc), and hide unused columns.
    You can still apply formats, validation or conditional formatting to the user area without ill effects.

    If you require to save thousands of rows of data it is far quicker to read and write the file as binary in a single instance ( you need to dimension an array to known number of rows).


    Dim TextArray(2000, 26)

    Sub TextSave()
    TextArray(2000, 26) = Range("A1:Z2000")
    Open "c:\textdump.txt" For Binary As #1
    Put #1, , TextArray 'writes to file
    Close #1
    End Sub

    Sub LoadText()
    Open "c:\textdump.txt" For Binary As #1
    Get #1, , TextArray 'reads complete file
    Range("A1:Z2000") = TextArray(2000, 26)
    Close #1
    End Sub

    One more note, the values of the cell are what is written depending on the cell formatting.
    text as text
    numbers as numbers
    dates as serial number :ie 01/01/03 as #37622#
    boolean as #True# or #false#

    Thanks all for replies,

    Good to see others have explored the problem.

    In response to queries:

    I use hyperlinks because
    1. they are convenient
    2. are easy to add with VBA
    3. my filenames change
    4. my file locations may change

    I have an addin which checks the top row of all my workbooks for relevant hyperlinks and updates them when the file is opened, it is unnoticed by the user.

    I do this to avoid lengthy chunks of code in all the workbooks.

    I already have a hidden sheet which stores a boolean for my initialisation code to check wether it has run itself.

    The WorkbookActivate solution may be a better test though.

    Once again thanks for the response.

    Hi all,
    Hope some one can share some insight.

    I have an expanding number of Workbooks that include hyperlinks to common reference Workbooks, all of which have autorun macros in the WorkbookOpen Event. However this event is not trigured by a hyperlink, as a work around i have used the WindowActivate event to run initialisation code normally called by the auto open routines.
    This is not very efficient as the code is called every time the user selects a given Window. Does anyone have a better solution???

    Hi AJW
    yes Iam awake again, here is some very simple code as example for you

    Option Explicit
    Option Base 1
    Public TextRange As String
    Public TextData(4)
    Public Rw As Integer, Cl As Byte
    Public Path As String
    Public FName As String
    Public Fnum As Integer
    Public Count As Integer
    'TEXT DUMP Excel Workbook ; Insomniac 2003

    Private Sub ALLOFF()
    Application.IgnoreRemoteRequests = True 'disable system
    Application.Calculation = xlCalculationManual 'disable calculation
    Application.EnableEvents = False 'disable events
    Application.ScreenUpdating = False 'prevent screen flicker
    End Sub

    Private Sub ALLON()
    Application.IgnoreRemoteRequests = False
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.StatusBar = False
    End Sub

    Public Sub TextDump()

    Count = Application.WorksheetFunction.CountA(Sheets("DATA").Range("A:A"))
    TextRange = "A1:D" & Count

    'when writing text files you need to replace any quotation marks
    'that the user may have entered : I use apostrophy instead
    Sheets("DATA").Range(TextRange).Replace What:=Chr$(34), Replacement:=("'"), _
    SearchOrder:=xlByColumns, MatchCase:=True

    'create a filename
    FName = Application.UserName & Format(Date, "dd-mm-yy") & ".txt"

    Path = ThisWorkbook.Path & FName
    Fnum = FreeFile ' Get unused file number
    'On Error GoTo Done
    Open Path For Output As #Fnum
    For Rw = 1 To Count
    For Cl = 1 To 4
    TextData(Cl) = Sheets("DATA").Cells(Rw, Cl)
    MsgBox (TextData(1))
    Write #Fnum, TextData(1), TextData(2), TextData(3), TextData(4)

    Done: Err.Clear: Close #Fnum: On Error GoTo 0
    End Sub
    Public Sub TextLoad()
    Rw = 1
    FName = Application.UserName & Format(Date, "dd-mm-yy") & ".txt"
    Path = ThisWorkbook.Path & FName
    Fnum = FreeFile ' Get unused file number
    On Error GoTo Done
    Open Path For Input As #Fnum
    Do While Not EOF(1) ' read in file until finished
    Input #1, TextData(1), TextData(2), TextData(3), TextData(4)
    For Cl = 1 To 4
    Cells(Rw, Cl) = TextData(Cl)
    Rw = Rw + 1

    Done: Err.Clear: Close #Fnum: On Error GoTo 0
    End Sub

    Create a workbook with a sheet named "DATA"

    insert dummy data in columns A:D

    CodeNo. Date operation complete
    1 01-Jan see y
    2 02-Jan hear n
    3 03-Jan smell n
    4 04-Jan touch y

    insert a module and copy the code to it

    ***SAVE the wokbook***

    Run 'TextDump' to save the text
    Run 'TextLoad' to load the text

    As you can see we are only saving text in columns A to D the rest of the worksheet may contain formulas, hyperlinks etc and will remain intact. Other sheets may be graphs or tables that reference the text columns A:D on sheets 'DATA'

    this is a much simpler way for multiple users to acces all the formatting and links of a custom workbook without having write access to the workbook

    For user friendly no-fuss insert this code in the 'BeforeSave' workbook event code

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Call TextDump
    Cancel = True
    End Sub

    This will Dump the text file and prevent the XLWorkbook from saving

    As I mentioned in my previous post I usually add an INDEX sheet which loads all the text files for the USER to choose from (more code needed for this).

    One last point, sharing a workbook makes it extremely large and very user UNfreindly, if you simply dump each users input as text you can check with worksheet change selection event for file datetime and load in new data if it has been saved. I have Common Wokbooks with up to 20 Users at a time and they all see the same data instantly.

    Hope you can follow this, and yes I tested the code!!!!!!

    I have set up similar templates to what you describe and encountered the same problem. As a solution I wrote a macro to only save the text of the workbook when the file is saved.
    I added an 'INDEX' sheet to the workbook which quickly loads the directory of text files. The INDEX includes an OPEN button so when you select a text file it is imported back into the template. The template is fully formatted with data validations, cross links to other files, graphs, tables etc. a 3mb file. The text files are only 2kb to 50 kb in length a massive saving. The text save macro is in the before save event, it dumps the text to file and cancels the workbook save.

    Hope you find this usefull.