Cell event 2

[honeypot3]

Hi there

I am looking to set up a simple piece of code which executes if a certain cell on a worksheet is clicked but I cannot find the equivalent of the click event in Access for an Excel cell. I have seen references to the Worksheet_SelectionChange event and Intersect but being fairly new to VBA code am unsure how to use these to achieve the following:

If cell A3 is selected but either cell A1 or Cell A2 is empty then a message box should appear asking user to enter their own value in cell A3. However if cell A1 and Cell A2 both contain values then cell A3 should calculate the total of cell A1 and cell A2.

Any help would be very much appreciated

Thanks Sandra

 

[SkipVought]

There is the SelectionChange event in Excel only.

There is the Worksheet_SelectionChange event in the ThisWorkbook object that acts on EVERY WORKSHEET in the workbook and in each Worksheet object there is a SelectonChange event for that worksheet only.

set rng = application.intersect(target, range("A3"))
if not rng is nothing then
  if [A1]="" or [A2]="" then
      msgbox "enter a value"
  else
    if isnumeric([A1]) and isnumeric([A2]) then
      target.value = [A1]+[A2]
    else
      msgbox "non numerics in a1 or a2"
    end if
  end if
  end if
end if

Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[honeypot3]

Hi Skip

Thanks for that code it works fab - just one question can I use a similar piece of code for another cell further down the spreadsheet. I tried copying and changing the cells involved but Excel objected to having 2 the same - the point you made above I believe. But can I set an additional target addresse in the Intersect command? What I need is if the user also fails to fill in cell A4 then when they click onto cell A5 a message box as before appears prompting for a value but if A4 is filled in then A5 does the formula [A4]- [A5]. Is this possible?

Sandra

 

[SkipVought]

You could do something like this...

if target.count>1 then exit sub  'multiple cells won't work
select case target.address
  case "$A$3"
     'struff to do for row 3
  case "$A$5"
     'different stuff to do for row 5
end select

Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[honeypot3]

Hi Skip

Looked promising but cannot get both to work - only the first case ever works if at all. If I use:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Select Case Target.Address
Case "$A$3"
If IsEmpty([A1]) Or IsEmpty([A2]) Then
MsgBox "enter a value"
Else
If IsNumeric([A1]) And IsNumeric([A2]) Then
Target.Value = [A1] + [A2]
Case "$A$5"
If IsEmpty([A4]) Then
MsgBox "enter a value"
Else
If IsNumeric([A4]) Then
Target.Value = [A3] - [A4]
End If
End If
End If
End If
End Select
End Sub

I get error message Case without Select case.

If I then put in extra select case (as below) only ever executes first case and ignores second.


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Select Case Target.Address
Case "$A$3"
If IsEmpty([A1]) Or IsEmpty([A2]) Then
MsgBox "enter a value"
Else
If IsNumeric([A1]) And IsNumeric([A2]) Then
Target.Value = [A1] + [A2]
Select Case Target.Address
Case "$A$5"
If IsEmpty([A4]) Then
MsgBox "enter a value"
Else
If IsNumeric([A4]) Then
Target.Value = [A3] - [A4]
End If
End If
End Select
End If
End If
End Select
End Sub

Where am I going wrong?

Sandra

 

[GlennUK]

Close your If blocks before going to the next Case statement.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[honeypot3]

Thanks Glenn - works like a dream. You are a star. Thanks to you also Skip for getting me so far.