Programming help

B

BB

Hey I am trying to design a small spreadsheet in which you have a choice
between two options, one in column E and one in column F. Is there a way in
VBA to make it possible to double click on the choice that you pick and for
that choice to then show up in another column, H.

Is this too complicated and is there an easier way to do this? Thanks in
advance for your help.
 
R

Rowan Drummond

One way would be to use the worksheet doubleclick event. Right click the
sheet tab, select view code and paste the following event code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range _
, Cancel As Boolean)
On Error GoTo Exit_Event
Application.EnableEvents = False

If Target.Count = 1 Then
If Target.Column = 5 Or Target.Column = 6 Then
Cells(Target.Row, 8).Value = Target.Value
End If
End If
Cancel = True

Exit_Event:
Application.EnableEvents = True
End Sub

Hope this helps
Rowan
 
R

Ron Coderre

Would this be a good place to use Data Validation?:
That would put both options on a drop-down list. The user would then just
pick one of the items and it would be selected.

Select the option cell
Data>Data Validation
Allow: List
Source: Option1, Option2 (just type in the items separated by commas.)
Click OK

Does that help?

***********
Regards,
Ron
 
D

David McRitchie

This should get you started:
http://www.mvps.org/dmcritchie/excel/event.htm

you would use target.column = 5 to check for column E
and target.column = 6 to check for column F

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Target.Column < 5 Or Target.Column > 6 Then Exit Sub
Cells(Target.row, 7) = Target.Column
End Sub

to install right click on the sheet tab, view code
place the code under the Option Explicit
 
Top