Posts by Ivan F Moala

Re: Internet Explorer Select All

Try the commands

ie.ExecWB 17, 0 '// SelectAll
ie.ExecWB 12, 2 '// Copy selection

Re: Trigger Click Events Via Vba

try

Application.Run ("Sheet1.wkscmd_DisplayDEI_Click")

Where Sheet1 is the code name of the Sheet the code is in ??

Re: Count Unique Items In Autofilter

You must have MOREFUNC Add-In

See Krishnas link above

Re: Count Mouse Clicks On Cell

Don't know the overall requirements of the request BUT you CAN DO this
via some API's and, as you have suggested swapping the mouse + Event code

Note: It needs the AddressOf operator so will only work in Xl2000+

This example Monitors a range = C2:C20 ..... No real testing done.

You need to Start the timer to monitor the Range ....

This is just ONE way to do it ... NO Real testing....

Re: Show Popup Box For x Seconds On Workbook Open`

Just be aware that wsh timer does not work in some situations.
What these are I am not sure of, BUT I have had it NOT work using Xl2003 OS: XP

Re: Network Printer 'ActivePrinter' of object'_Application' failed

Your Net work port number will always change.

eg NE01: to NE16:

Just loop through the possible network numbers with an On Error resume next and
error check ......

Re: Constantly Running Macro

This sought of task is better suited to scripting.........

See here for an idea on how to do this............

http://www.dailydoseofexcel.co…/07/07/monitor-directory/

Re: Releasing Object Variables

As a Rule; Anything that your program has to acquire via CreateObject, Set, Get etc you should release.
Even if the documentation states that Windows takes care of releasing the resource used by a process when it exits.

The cost to you is just a few lines of code, BUT, NOT having resorces released properly can
be detrimental to the running performance.

On the lower level API programing this is very true.
In APIs that Create, open, extract etc it is IMPORTANT that you use it's corresponding "Release" API to release resources to it. eg using the API CopyEnhMetaFileA, Windows keeps a record of GDI objects on a per-process basis, only the application that created the GDI object is able to use the corresponding GDI functions with it or remove it from the system eg delete it.
The API we need to ALWAYS USE for this is DeleteEnhMetaFile.

So if you used;
LoadBitmapA > then you need to use DeleteObject to release resources
CreateBitmap > DeleteObject
CreateSolidBrush > DeleteObject
CreateCursor > DestroyCursor
CreateCompatibleDC > DeleteDC
GetDC > ReleaseDC
CreateFontA > DeleteObject
CreateIcon > DestroyIcon
ExtractIconA > DestroyIcon
CreateMetaFileA > CloseMetaFile
CopyMetaFileA > DeleteMetaFile

So to be on the safe side I always Set to nothing

Re: Send Keys Tab Key Generates Beep But Doesn't Send Command

Have you tried using the Enter key

{ENTER} or ~

Re: Worksheet Change Event Intersect Error 91

Quote from helmekki

Hi There
This is my code:

Code

Private Sub Worksheet_Change(ByVal Target As Range)


If Intersect(Target, Range("D6:D6000")) Is Nothing Or Intersect(Target, Range("D6:D6000")) = "" Then
If Intersect(Target, Range("F6:F6000")) Is Nothing Or Intersect(Target, Range("F6:F3000")) = "" Then
If Intersect(Target, Range("I6:I6000")) Is Nothing Or Intersect(Target, Range("I6:I6000")) = "" Then
Exit Sub
Else


Do Something......
End If
End If
End If
End Sub

Display More

when excution comes to this line

Code

If Intersect(Target, Range("D6:D6000")) Is Nothing Or Intersect(Target, Range("D6:D6000")) = "" Then

a run time error (91) appears, it tells object variable or with block variable not set.

any idea why ? Thank you

Display More

Your original code was not a Worksheet change event, hence my response that Target
was NOT defined ...

You had

Sub T()
etc .............

Re: Run Time Error 91

You have not defined Target. i.e you will need to set target to a Range object

Re: Disable Alt+f11 Shortcut Key

Quote from royUK

Thanks Ivan

Youv'e not been arond much lately, are you busy?

Hi Roy, been busy with personal matters. Good to see Ozgrid going strong.

Re: Disable Alt+f11 Shortcut Key

Quote from royUK

Agreed, the VBA password can be broken, but not easily. Messing about with Menubars, settings etc can cause problems if you don't restore everything correctly. If you want to lock a project completely see here

Hi Roy, that Method is a good way to stop access.
And yes it will stop most hackers.

Re: Launching Another File Format From Vba

Quote from coolhandphil

Hyperthetically if I wanted to open a PDF file from excel how would I do that in VBA?

Either

1) Hyperlink (BUT you will a security prompt)
2) Shell (Look up help)

or
3) API

Have a look here

http://www.xcelfiles.com/ShellExecuteA.html

Re: Stop Users Changing Computers Time/Date

Quote from Rich_z

You don't.

You use the MMC policy editor to role out the restrictions automatically when the users connect to the Lan, which hopefully they need to do at some time or another.

If not, it's a laptop recall, or a message to visit the IT department when they are next in the office.

As for returning control back to them when the work book is closed, why would you need to do that ?

How many times have you needed to change the date and time on your laptop once it has been set ?

The only reason is to set the date forward so that you can install some time dependant demonstration software so that when you put the date back the demo never expires. And if it's work laptops you are talking about, people should not be installing their own stuff on them any way.

No argument for not doing it.

Display More

Yep, that would work .... so the solution would involve IT, which most IT depts would set up for their LAN and WAN by default, at least from what I have seen, unless like in my case our productin PC is not set (in fact it is the only one ?) in which case I have set up the proposed routine I mentioned earlier, to check the server time. All other PC's cannot change there system time as they Don't have access to it.

Re: Stop Users Changing Computers Time/Date

Quote from Rich_z

And they have a network connection.....

How do you do it if they are working standalone ?

Use the tools that are correct for the job. There is no way that you will be able to stop the users changing the date and time of their laptop (as you have asked) unless you use access rights and/or group policies.

You can restrict access to just changing the date and time. It doesn't hurt any one. In fact the laptop I am using now does exactly just that and I haven't had any problems with it, and the users will not feel the effect of the restriction until they actually go to 'cheat' and change the date and time.

I think in all reality, to do the job properly you really do need to look at Group policies. It's what the tool is there for. Why reinvent the wheel ? (or at least reinvent a poorer version of the wheel.....)

Display More

Yes, so how do you propose to impliment this programatically for users and then return control back to them after workbook is closed ?
Via VBS, WMI, or other scripting languages.

Re: Adding Items to Combo From Another File

You need to think how you would get duplicates manually using native Excel.
So one method to do this is to use Advanced filter.

Basically (Provided file is open)
Advance fileter on Data (unique), Place data in std location.
Loop through this location adding to combobox.

Thats the steps to take for one way to do this.

Post back if you have problems in doing the code and I'm sure someone can help you through it.

Re: Stop Users Changing Computers Time/Date

I use a routine to Test the Date time of our server and compare it to the current PC's date time. This has worked fine for me, as long as the server time is OK

Re: Replacing sets of names- there must be any easy way

Greg, something like this doesn't require singular scripts and is easy to do with a macro provided we know what data, ranges etc. In fact you could do it with out code if I understand you correctly.

Is is possible to upload a rough work book ?

Re: missing microsoft forms 2.0 Object Library

Have you tried reinstalling XL for the user.