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!!!!!
Sample Here
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!
Just an observation, but it appears that setting conditional formatting of columns I-L to
Formula is =$L1=MAX($L:$L)
(format pattern to yellow)
would achieve the same result.
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.
No worries, glad to finally find a solution for you.
The sample you provided made it lot easier to work out what you wanted.
I see Colo beat me to it, same answer though!!!!!
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
etc.
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
eg
={SUM(IF($G$5:$G$11="RD",IF($L$5:$L$11=200,$H$5:$H$11,0),0))}
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
Next
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!
You are quite correct, I have had the problem.
Solution is to use the Calculation Event.
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).
eg:
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.
eg:
text as text
numbers as numbers
dates as serial number :ie 01/01/03 as #37622#
boolean as #True# or #false#
etc
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()
ALLOFF
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)
Next
MsgBox (TextData(1))
Write #Fnum, TextData(1), TextData(2), TextData(3), TextData(4)
Next
Done: Err.Clear: Close #Fnum: On Error GoTo 0
ALLON
End Sub
Public Sub TextLoad()
Range("A:D").ClearContents
ALLOFF
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)
Next
Rw = Rw + 1
Loop
Done: Err.Clear: Close #Fnum: On Error GoTo 0
ALLON
End Sub
Create a workbook with a sheet named "DATA"
insert dummy data in columns A:D
eg
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.
Use Conditional Formatting.
Select the cells, or whole column etc where your dates are.
Go to Formatting-Conditional
[Condition1]
cell value ,greater than equal to,=today()
Click Format button
(set pattern colour to red)
hope this helps!!!!!
