Replies

When you start having to nest more than 3 or 4 times, you will find that a function is much more efficient and easier to read and write.

For example,

=IF(C7=”Town1″,B2,IF(C7=”Town2″,B3,IF(C7=”Town3″,B4,””)))

could be replaced with
=GetTown(C7,B2,B3,B4), provided you have entered the function below into Visual Basic.

Alt F11 to get over to Visual Basic, Insert Module.
and type:
Function Gettown(Value,A,B,C)
IF Value = “Town1″ Then
Gettown = B2
ElseIF Value =”Town2” Then
Gettown = B3
ElseIF Value = “Town3” Then
Gettown = B4
Else
Gettown = “”
End If
End Function

An improvement to this is to use the Select Case Statement

Function Gettown(Value,A,B,C)
Select Case Value
Case “Town1”
Gettown = B2
Case “Town2”
Gettown = B3
Case “Town3”
Gettown = B4
Case else
Gettown = “”
End select
End Function

Thank you Jon!

This really helped me. I did have problems trying to use the dim statement, however. When I tried:
Dim Myworkbook as Workbook
set Myworkbook = ActiveWorkbook

It didn’t seem to work with the exact syntax as above.
MyWorkbook = ActiveWorkbook.Name
MsgBox MyWorkbook

Thanks…

I am trying to write a macro that disables numlock upon opening an excel spreadsheet (that is primarily used on lap-tops). The following is what I am trying, but it doesn’t seem to be successful.

Sub numlock()
Application.SendKeys (“{NUMLOCK}”)
End Sub

Any suggestions would be appreciated. Thanks!!

Legare… thank you very much for your response. Taking the quotes away from the word “False” in this situation causes an error.

I will try the various suggestions you listed and will post the solution that works! Thank you (as always) for your kind assistance.

Have you considered using WinZip to zip and create an executable? When zipping, specify to recurse folders. Then from the Action menu select “Create .exe file”.

You can then email the file to your coworkers and when they unzip it will go into their respective Office directories.

Thank you Hans!
It works beautifully. I modified it slightly to always turn off numlock. I don’t understand the array of 255 values called keyboardbytes. But it sure works like a charm. Thanks So Much Again.

I cannot thank you enough! It worked great and I so appreciate the help.

I suggest creating a function to compute your desired results.

A simple example of a function would be:

(In your spreadsheet:)
A B C
1 1 1 =findanswer(A1,B1)
2

In your visual basic module (insert module)

Public Function FINDANSWER(ITEM1, ITEM2)
If ITEM1 = 1 And ITEM2 = 2 Then
FINDANSWER = 12
ElseIf ITEM1 = 1 And ITEM2 = 1 Then
FINDANSWER = 11
Else
FINDANSWER = “neither 11 nor 12”
End If
End Function

You aren’t limited to 7 nested if statements like you are when you are using the if function in a cell. I find that it is also less confusing to tell what you’ve done when the formula is as complicated as the one you have listed!

As long as you are aware of the AUTOCORRECT feature, it is a feature instead of a bug. You can modify the autocorrect entries (as the other users stated above) which will prevent the text replacement in the future. But, it is also useful to know that the UNDO (or Ctrl-Z) allows you to cancel the text replacement “on the fly.”

Are you already aware of the option to simply supress the display of the zero values (Tools – Options – Views -remove checkmark from ZERO VALUES)?