Hi guys,

I have a spreadsheet, with a grid on it.

What i woudl like to do, is every time a user of the file clicks ona
particular cell in the grid it runs a piece of code (mainly an input
box).

Is there anyway to do this so it is automatic, whcih out having to
press a button?

Cheers

Fernando


--
Fernandoalberte
------------------------------------------------------------------------
Fernandoalberte's Profile: http://www.excelforum.com/member.php...o&userid=24493
View this thread: http://www.excelforum.com/showthread...hreadid=383130

Hi Fernando,

Yes there is. By using the Worksheet_SelectionChange Event. Some
example code is shown below:


Code:
--------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Address
Case Insulation_Type_Address
Call Set_Insulation_Drop_Down(Target)
Case Thickness_Address
Call Set_Thickness_Drop_Down(Target)
Case Outlet_Type_Address
Call Set_Outlet_Type_Drop_Down(Target)
Case Outlet_Size_Address
Call Set_Outlet_Size_Validation(Target)
End Select
Call Check_Data_Entry(Target)
End Sub
--------------------


The code above calls a different subroutine if the target address is
the same as one of the ones specified in the case statement and then
runs the CHECK_DATA_ENTRY subroutine for all cells.

Regards

Rich


--
Rich_z
------------------------------------------------------------------------
Rich_z's Profile: http://www.excelforum.com/member.php...o&userid=24737
View this thread: http://www.excelforum.com/showthread...hreadid=383130

This is great just what i need, but i am not sure i follow the code.

Which bits would i need to change if i wanted the code to run different
sub routines if i select cell "a1" vs if i select cell "b1"?

Sorry to be a pain.

Fernando


--
Fernandoalberte
------------------------------------------------------------------------
Fernandoalberte's Profile: http://www.excelforum.com/member.php...o&userid=24493
View this thread: http://www.excelforum.com/showthread...hreadid=383130

Hi Fernando,


Code:
--------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Address
Case Insulation_Type_Address
Call Set_Insulation_Drop_Down(Target)
Case Thickness_Address
Call Set_Thickness_Drop_Down(Target)
Case Outlet_Type_Address
Call Set_Outlet_Type_Drop_Down(Target)
Case Outlet_Size_Address
Call Set_Outlet_Size_Validation(Target)
End Select
Call Check_Data_Entry(Target)

--------------------


The case statement determines what code is run when a particular
address is encountered. The object 'TARGET' is a range with a property
'ADDRESS' so for cell A1, TARGET.ADDRESS will be '$A$1'.

So to run different code for different cells, change the case
statements. For example:


Code:
--------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Address
Case "$A$1"
Call Code_For_A1
Case "$B$7"
Call Code_For_B7
Case Else
'*
'* Code here for any cell not covered by list above
'*
End Select
'*
'* This is called for all cells regardless of whether any
'* code has been called above
'*
Call Check_Data_Entry(Target)
End Sub
--------------------



Regards

Rich

Oh... Ps Please don't hard code your cell addresses as I have done
above - Practice good programming practices and use named constants!


--
Rich_z
------------------------------------------------------------------------
Rich_z's Profile: http://www.excelforum.com/member.php...o&userid=24737
View this thread: http://www.excelforum.com/showthread...hreadid=383130

Thank you, now i understand. That is great.

One last thing, is it possible to say is a cell is in a range (eg if
the range is a1:d5, and the cell selected is b3 - therefore in the
range) then run the code.

So rather than specifiying eth cell you are specifying the range...does
thsi make sense?

Thanks again for all your help

Fernando


--
Fernandoalberte
------------------------------------------------------------------------
Fernandoalberte's Profile: http://www.excelforum.com/member.php...o&userid=24493
View this thread: http://www.excelforum.com/showthread...hreadid=383130

Fernandoalberte Wrote:
One last thing, is it possible to say is a cell is in a range (eg if the
range is a1:d5, and the cell selected is b3 - therefore in the range)
then run the code.

So rather than specifiying eth cell you are specifying the range...does
thsi make sense?

Yes it is. I'm not sure how to do that directly in Excel (I came to
Excel after programming for many years and tend to do things the long
way round.....) You have the current cells address (ie Target.address).
You can then compare this with your range quite easily.

Unfortunately I don't have any example code handy.

Regards

Rich


--
Rich_z
------------------------------------------------------------------------
Rich_z's Profile: http://www.excelforum.com/member.php...o&userid=24737
View this thread: http://www.excelforum.com/showthread...hreadid=383130