If two out of three cells are empty, create msgbox

[pook_666]

Hi there,

I have three cells, say A1, A2 & A3.

In order to be able to run a macro, one of these cells have to contain a value. No more than one of these cells can contain a value.

If there is a value in two or more or these three cells, or no values at all in the three cells, I want a msgbox to appear stating "error".

I was thinking of maybe doing some type of loop to do this, but am not sure?

I know this sounds like a puzzle Sherlock Holmes would be proud to investigate, but any help would be appreciated!

 

[Zot]

In worksheet module

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1", "A3")) Is Nothing Then
    Set Rng = Range("A1", "A3")
    nCount = Application.WorksheetFunction.CountA(Rng)
    If nCount > 1 Then MsgBox "Error"
End If

End Sub

 

[dmt32]

Hi,
try adding following in your code & see if helps

Dim a As Integer
a = Application.CountA(Range("A1:A3"))
If a = 1 Then
'run macro
Else
    MsgBox "One Cell Only Must Be Completed", 48, "Error"
End If

note: you will need to qualify the range if the sheet is not the activesheet

Dave

 

[pook_666]

@dmt32 Marvelous! Thank you for this.......glad I didn't go down the loop route otherwise would've been stuck for ages!

 

[Zot]

@dmt32 Marvelous! Thank you for this.......glad I didn't go down the loop route otherwise would've been stuck for ages!

When you mentioned loop, I thought you want macro to automatically run when condition is True. That is why I use Worksheet Change event

 

[pook_666]

@Zot ah sorry, what I meant in my original question was the only way I could think of doing some sort of loop but wasn't sure....apologies for the confusion & thanks for your help!!

 

[dmt32]

@dmt32 Marvelous! Thank you for this.......glad I didn't go down the loop route otherwise would've been stuck for ages!

you are welcome - glad suggestion helped

appreciate the feedback

Dave