Macro for input of variables and calculations
Posted by Oguz SAYINER on January 15, 2002 4:09 AM
Hello,
I need to create a sheet for making some basic calculations. I, myself, handled the calculations part. The point is that; I want to control the movements of the activecell. I want say "s6" to be active first, wait until the input of a letter (one of a,b,c,d,e). After the input, it will go to say L20, wait there until input of an integer. and go to L 22 for another integer.
I could not solve the part of the macro concerning the input of values...
Please, help... thnx in advance.
Posted by Tom Dickinson on January 15, 2002 8:43 AM
Oguz:
The macro could check the input to see if it was valid:
If ActiveCell = "a" or ActiveCell = "b" ...
or you could have the cell itself do the checking:
On your tool bar, select Data, Validation, Settings. Under "Allow" select List, then enter the possibilities.
Out of curiosity, where are you located? (I don't know the extension for your e-mail address.)
Posted by Oguz SAYINER on January 16, 2002 8:42 AM
Thnx Tom, the tr stands for Turkey; I am actually in Ýstanbul, you may know the Bosphorus.
There is still a problem. How do I make the macro to wait until I put a/the variable (one of a,b,c,d or e)???
thnx for your help... ;-)
Posted by Tom Dickinson on January 16, 2002 2:24 PM
Merhaba!
I spent 2 years in your country in the mid 70s, mostly at the NATO stations in Izmir. Your name reminded me of a commissar for antiquities that I met on an archiology scuba diving expedition.
I did some testing, and the folowing seemed to work for me. It all needs to go in the macro sheet for the sheet you are working on, not on a separate macro sheet. You will also need to do some changes for your situation.
The 1st and last routines turn on and off the ability to check the cell. The second routine actually checks the cell.
In the 2nd routine, you will need to add the other possibilites for good answers (C,d, & e). Where I put Select B5, I think you want it to go to L20) The line that seems superfluous [Range("A1") = Range("A1"] is needed in order to allow the selection of another cell. I'm not sure why, it could be because one of the procedures in the "change" macro.
Anyway, here it is, and good luck.
Dim InA5 As Boolean
Private Sub Worksheet_Activate()
If ActiveCell.Address = "$A$5" Then
InA5 = True
Else
InA5 = False
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If InA5 Then
If UCase(ActiveCell) = "A" Or UCase(ActiveCell) = "B" Then
Range("B5").Select
Else
Range("A5").Select
Range("A1") = Range("A1")
End If
End If
End Sub
Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If ActiveCell.Address = "$A$5" Then
InA5 = True
Else
InA5 = False
End If
End Sub
