Posts by AJW

Dave,

I need to distribute the addin to a wide user group, if I used the Personal.xls file I might overwrite their own existing code.

"I guess the other thing to check is whether Events are disabled?"

How do I do this ??

"All that happens is that Excel locks up when I launch it ?? "

I've overcome this part of the problem by recoding the Workbook_Open and Workbook_Close events but it still wont trigger.

"It sounds like a re-installation might be in order."

No go, work machine and my IT department are already irrate with me and my project. I've been banned from the software directory on the server.

"Are you sure it's not the code in the Workbook that is causing lock-up? "

100% OK when manually loaded, problem resides with load through XLStart.

Thanks again

AJW

OK, quick update.

I've tried everything I can think of (including digital signing) to get code to trigger with the Workbook_Open event in Excel XP but to no avail.

If anyone has any idea please let me know ASAP.

Point of clarrification:

The Workbook_open event does not work when the file is loaded through the xlstart directory.

C:\Program Files\Microsoft Office\Office10\XLStart

It does however work if I manually load the file. The problem is that I need it to work through XLStart as it triggers a series of other addins to be loaded and managed.

My dumb users gripe about clicking too many buttons let alone having to manually load a file :~(

Thanks

AJW

Hi All,

Well I've finally upgraded from good old Excel 97 to Excel XP.

Must say it looks and feels very pretty

Slight problem though, I have placed a file in the xlstart directory that has code in the "This Workbook" Module that's meant to run with Workbook_Open. Problem is that it dosen't run.

I set the security level to low but that didn't work. So as per the recommendations in help I set it to medium which is then supposed to prompt me to allow the code to run, again that didn't work. All that happens is that Excel locks up when I launch it ??

Anyone got any ideas? I could spend hours trying to figure it out but would appreciate any pointers from people who may have experienced the same problems.

P.S I haven't played around with digital signatures yet.

Thanks

AJW

Kabong,

Why not stick with the input box?

The attached code allows you to select a range that will be used, or as you put it "to prompt the user to paste data into a specific area of the workbook".

Once the OK is clicked code is returned to and executed on the location of choice.

Hope this helps.

Allcare

AJW

Sub MYCellSelect()
On Error Resume Next
Dim mycell As Range
Set mycell = Application.InputBox(prompt:="Select Cell to enter value. ", Title:="Value Location", Type:=8)
Application.Range(mycell.Address).Value = ("Hello - " & " 100 " & "bonus points")
End Sub

Thanks Weasel,

Pretty much as I thought, the desktop running Excel no longer has write authority to the file on the server as the connection has gone down.

Regards

AJW

Hi all,

Haven't been around much lately as work has been crazy and I had a nasty car accident that resulted in a fractured back, ouch! Hurts just thinking about it.

Anyways, back at work now and as usual because I'm the resident XL guru (wink wink) and have developed an in house package that we use I naturally get the blame for any Excel problems that occur.

I was hoping someone might be able to shed some light on this one.

People are randomly getting the following message when they attempt to save a workbook on to our server. My thoughts are that the communication to the server has at some time cut out and that Excel can not find the original workbook ???

Any ideas ???

Thanks

AJW

My favorites: "Excel gives me the sheets" & "Death Row is 65,536".

Geeezz........ I've just realized I'm a geek!

AJW

P.S Jeff, got anyone for the part time position yet?

Hi All,

I am creating a workbook (WB1) from sheets in an existing workbook (WB2).

WB1 is saved to a location of choice by using Application.GetSaveAsFilename, a message box then informs the user of the path to WB1 using Application.ActiveWorkbook.path and WB1 is then closed.

The problem I have is that I need to somehow capture the file path for WB1 and enter it into WB2 as a hyperlink to WB1.

Does anyone know how to capture the file path using Application.ActiveWorkbook.path ?

I need to get the path name for WB1 and record it somehow programatically before I close the file so that I can use it in the hyperlink creation for WB2.

Any ideas ? Sounds straight forward but I'm stuck !

Thanks

AJW

Gopalsh,

Thanks for the update, could you give us an example of how you achieved this with VBA? It would help us unlearned ones to further understand the outcome.

AJW

Quote

Originally posted by Andy Pope
Hi rem1224 and AJW,

Yes in XL2000 you can get the formula by copying whilst editing the formula.

the help in XL2000 tells me that
DisplayClipboardWindow is a MAC (Office98) command.

The others are supported and with VBA you can manipulate the clipboard.

BUT my understanding is that gopalsb question was about the Office clipboard. The one the contains multiple clips, up to 12.

Cheers
Andy

Display More

Hi Andy & Others,

Quote: "The reason for the above queries is that if two operations of copy (when programatically this copy macro is called)is done the latest copying action will replace the contents in the common windows clipboard.My aim is to prevent this."

Specifically: " if two operations of copy (when programatically this copy macro is called)is done the latest copying action will replace the contents in the common windows clipboard.My aim is to prevent this."

My reading was that gop wanted to stop the common Windows Clipboard contents being replaced.

I don't know that this can be done so I was suggesting a possible work around to capture the contents before being replaced as a *.clp file and then reload this file to the Clipboard when needed.

I'm still stuck in Office 97 so I know diddly about the Office specific Clipboard included in 2000+ and XP.

Hope this clarifies my response.

Cheers

AJW

All,

I use the following code to launch the clipboard, insert an image as well as format it a bit.

You may be able to use it to manipulate the clipboard.

Sub ViewClipboardCurve()
Dim mycell As Range
On Error Resume Next
MyAppID = Shell("C:\WINDOWS\CLIPBRD.EXE", 1)
MyAppID = Shell("C:\Marketing\Quote Package\Programs\CLIPBRD.EXE", 1)
On Error GoTo ErrorMessage
Set mycell = Application.InputBox(prompt:="This will insert the contents of the Clipboard Viewer into the worksheet as an image." & vbCr & vbCr & "Please click on the worksheet to select the location to place the curve." & vbCr & vbCr & "Click the Cancel button if you do not wish to proceed", Title:="Curve Location", Type:=8)
Application.Range(mycell.Address).Activate
Application.ScreenUpdating = False
ActiveSheet.PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False, DisplayAsIcon:=False
Selection.ShapeRange.IncrementLeft 10
Selection.ShapeRange.IncrementTop 13
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 9
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 8
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Height = 808.5
Selection.ShapeRange.Width = 570#
Application.Range(mycell.Address).Activate
Exit Sub
ErrorMessage:
MsgBox ("INSERT FAILED - Clipboard does not contain a valid curve image."), vbCritical, "WSG-eQuote"
MsgBox ("Please ensure that you copy your curve from Perfcurve and try again." & vbCr & vbCr & "This can be done by right mouse clicking on the selected curve, Copy > To Clipboard."), vbInformation, "WSG-eQuote"
End Sub
Sub ClipboardViewerClose()
On Error GoTo error
AppActivate "Clipboard Viewer"
SendKeys "%{F4}", True
error:
Exit Sub
End Sub

Also worth noting is that in my version of Clipboard you can save the information copied to the clipboard as a *.clp file.

If you have specific data you need pasted in from the clipboard then you can programatically send it to the clipboard & possibly save it as a file? In theory you should ten be able to launch Clipboard, Load the file with you data and paste it into the workbook.

Just some thoughts that may prompt an alternative workaround.

Allcare

AJW

Quote

Originally posted by Dave Hawley
Out of curiosity, what were the time using the AutoFilter Method? I ask as I use this method frequently and it works on thousands of records in about 1-4 secs.

Quote "Judges decision is final and no corespondence will be entered into. (but a bribe might change my mind). "

Sorry Dave but it might upset the winner ;;)

Only kidding, to be honest it was way faster, probably about 2 or 3 seconds but I didn't set things up for it to work the way the Autofilter seems to...... well my understanding of using it once.

If time permits I'll play around with it to expand my knowledge but XLDennis's code was quicker to understand and solved my immediate problem of people thinking that their screen had frozen. Curse that Alt/Tab/Delete why are people so impatient ??

Thanks

AJW

XLDennis !!!!!!!!!!!!! WINNER AND CHAMPION EXTRODINAIR.

With a RECORD BREAKING performance of 10.64* seconds XLDennis code has shaved a whopping 41.07 seconds off the previous record held by LOOP.

XLDennis stunned onlookers and officials with the clever use of the FOR / NEXT method in place of the LOOP.

When interviewed later LOOP lamented his thrashing blaming screenupdating for his demise.

*Record set on Pentium3, 800MHZ, 128MB RAM, XL97, WIN 98 config. Judges decision is final and no corespondence will be entered into. (but a bribe might change my mind). Precision timing recorded on a Quartz Digital $29.95 Lorus watch from Kmart - Water 50m Resist.

(Imagine what people can do with all that spare time now) ;;)

Modified code as follows:

Sub hideit()
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnData As Range
Dim vaData As Variant
Dim I As Long

'Set wbBook = ActiveWorkbook 'ThisWorkbook
'Set wsSheet = wbBook.ActiveSheet '.Worksheets(1)
Set wbBook = Application.ActiveWorkbook 'ThisWorkbook
Set wsSheet = wbBook.Application.ActiveSheet '.Worksheets(1)

With wsSheet
'Set rnData = .Range("D1:D255")
Set rnData = Application.Range("EquipSumQty")
End With

vaData = rnData.value

For I = LBound(vaData) To UBound(vaData)
'If vaData(I, 1) < 0 Then rnData(I, 1).EntireRow.Hidden = True
If vaData(I, 1) < 1 Then rnData(I, 1).EntireRow.Hidden = True

Next I
End Sub

Again thanks to everyone who responded, great to see so many varying methods and ideas.

Allcare

AJW

Kieran,

Thanks, tried the new code and it works, only thing is it's not much quicker.

Loop = 51.97 seconds.
No Loop = 51.71 seconds.

Thanks anyway, always interesting to consider alternatives.

AJW

Back again, had a look at some of the alternatives offered. The Autofilter is not suitable as it seems to work on predefined views and values, unfortunately my views and values will vary with each new worksheet.

I tried your solution XLDennis but it keeps bugging out on me here:

If vaData(i, 1) < 0 Then

Sub Hide_Rows_0()
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnData As Range
Dim vaData As Variant
Dim i As Long

Set wbBook = ActiveWorkbook 'ThisWorkbook
Set wsSheet = wbBook.ActiveSheet '.Worksheets(1)

With wsSheet
Set rnData = .Range("D1:D255")
End With

vaData = rnData.value

For i = LBound(vaData) To UBound(vaData)
If vaData(i, 1) < 0 Then rnData(i, 1).EntireRow.Hidden = True
Next i
End Sub

I also tried to use your solution Kieran but it debugged out here:

If ActiveSheet.[d1].Offset(I, 0).value <= 0 Then

I'll have another look later when I get more time to see if I can tweek either to work.

Thanks

AJW

Thanks XLDennis, Richie, DaveH and Kieran for you EXCELent alternatives, appreciate the help. Am somewhat kicking myself though for not having thought through the alternatives.

Allcare

AJW

Hi All,

I was hoping someone might have a quicker way of looping through a range?

I use the following to loop through the cell value in D1:D251. If the value in the cell is greater than zero it does nothing, if less it hides the row and moves on to the next.

Only problem is that it take yonks to loop through.......aggghhh people are way to impatient to wait 50 or 60 seconds.

Suggestions?

Range("D1:D251").Select
Selection.EntireRow.Hidden = False
Application.ScreenUpdating = True
Range("D2").Select
Do While Counter < 251
Counter = Counter + 1
If ActiveCell.value &gt; 0 Then
ActiveCell.Offset(1, 0).Activate
Else
Selection.EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Activate
End If
Loop

BTW: XL97 WIN98.

Thanks

AJW

Quote

Originally posted by DrGuru
Is there a way, however, to trick Excel into thinking that there is at least one sheet visible?

DrGuru,

Yes, but it is not to trick Excel it is to use what Excel already allows. As you may be aware the normal extension for an Excel file is *.xls which is the standard. You can however also have a number of others but the type we need to aquaint you with is *.xla which is an addin file.

An addin is essentially an *.xls workbook that has been created to carry out a specific function or functions, and is loaded into excel but is not visible, it normally is saved as with the *.xla extension but there is a little trick that allows you to have an *.xls addin.

What we want is an *.xls file that has it's properties set to be an Addin rather than a woorbook, this way when it opens it is "invisible" until such time as the properties are changed. This achieves what you need, the next step is how to tell it to change the properties through the VBE (Visual Basic Editor), try playing with the following line of code and see if it fits with what you need to achieve.

Application.ActiveWorkbook.IsAddin = True

You can also set it to False

Hope this helps.

AJW

C:\temp\Addin Property.jpg

Hope this helps.

AJW

Sleeper,

Does the file you are trying to open contain a lot of links to other files ?

If so you can save a lot of time loading the file if you use:

workbooks.open("CFolderNameworkbook.xls") , UpdateLinks:=0

This will stop the links from updating and load the file as it was last saved.

Hope this helps.

AJW