Excel01

These pages will be dedicated to things you thought (or didn't know) that you

CAN DO in Excel !

1) Changing the Name Box list width

2) Changing the colour of the Tab (A hack :-) )

3) UNC - and connecting to Drives

4) Phoning from Excel

5) Userform tricks;

h) XL97 (a Hack :-) ) making Userform modeless

6) Opening ANY program

7) Downloading Files from the internet

8) Opening Chm files.

Note: Please press the More button to see more. Thanks for viewing.

Open ANY Program

There are times that you will want to Open a program in VBA.

Here is a method that will Open ANY file, as long as it has an associated application to open it.

See here for example Emailer - advantage of this is, you need No MAPI, ocx, or dlls

XL97 - Making a Userform Modeless:

The following routines are hacks that I found while experimenting with Application Commandbars in a UserFrom. Not sure why it works but calling the function makes a form modeless !?!

Note: Whilst it makes the form Modal you cannot interact with the Spreadsheet ie no inputs, just selecting.

Method1:

Downside:

You will have to put up with the About Microsoft Excel screen - thats it !!

So for Xl97 users try this: (Also works with Xl2000, even when ShowModal = True !!

In the Userform Code;

Private Sub UserForm_Click()

Application.CommandBars.FindControl(ID:=927).Execute

End Sub

Check it out and see what you get !

It doesn't have to be in the Click event, assign it to a button.

Method 2: By Colo

This method was supplied by my good friend Colo. Thankyou Colo

Option Explicit

'---Standard module

Sub Test()

UserForm1.Show

End Sub

Option Explicit

'---Userform1 module

Private Sub CommandButton1_Click()

' If Error message is shown, delete or comment out of following.

CommandButton1.TakeFocusOnClick = False

ActiveCell.Activate

ActiveCell.Value = "What?"

End Sub

'---Userform1 module

Private Sub UserForm_Activate()

With Application

.SendKeys "{esc}", True

.GetOpenFilename

.SendKeys "{esc}", True

End With

End Sub

Downloading Files from the Internet:

Here is a routine to download a file from the internet via APIs & VBA (of course!)

This routine uses the Addin download from Colo's Site as an example.

Phoning using Excel:

You can use your micorphone and the Dialer to make a call from within Excel:

UNC :

UNC is an acronym for Universal Naming Convention

UNC provides a naming convention for identifying and using network resources such as a network server, without connecting to it with a Mapped Drive - A mapped drive uses a drive letter for example, drive Y, where the letter represents the server and share to which it is mapped. UNC identifiers typically consist of three parts;

1 Server name \\<Server>

2 Share name \<Share>

3 and an optional file Path \MyDir\MySubDir

These are combined using backslashes or slashes. UNC notation is used primarily for mapping network drives in the Windows family of Operating Systems and are most commonly used to reach file servers or printers on a LAN.

Typical UNC path syntax: \\Stor01\Shared\moalai

Use UNC conventions when you are moving workbooks between diferent machines that may have been mapped to a different drive letter.

Below is some code to get the UNC paths given it's drive letter.

Mapping a Drive:

If you ever needed to map a Network drive to a Drive letter and provide a password, then here is one way to accomplish this. I use this to connect to our network drives without providing the user with my password. Obviously you need authorising rights before you can connect up.

See code below::

Since Oct 2002 this page was seen

ANNOUCEMENT:

This pages has been updated to facilitate the ease of getting the codes. See below