Range of object worksheet failed

[SmallScreen]

Hi,

I have a 2 page spreadsheet. On page 1 I have a table counting the number of yes and no votes. Sheet1!B2 contains number of Yes votes, Sheet1!B3 number of No votes.

In Sheet 2 I've created two buttons, one increases the yes count by one, and the other the no count by one. I first created these as forms buttons and they worked, but realised I couldn't edit the colour of the buttons so went to create them in ActiveX instead.

I've created the buttons using this code:

Private Sub CommandButton1_Click()
Range("Sheet1!B3") = Range("Sheet1!B3").Value + 1
End Sub

When I click the button, it generates the error: Method 'Range' of object '_Worksheet' failed.

I'm a complete noob and just making this up as I go along, so appreciate ELI5 instructions!

 

[shg]

Works fine for me. Got a protected worksheet, or merged cells?

 

[JoeMo]

Try:

Private Sub CommandButton1_Click()
With Sheets("Sheet1")
    .Range("B3") = .Range("B3") + 1
End With
End Sub

 

[MickG]

Try:-

Private Sub CommandButton1_Click()
With Sheets("Sheet1").Range("B3")
    .Value = .Value + 1
End With
End Sub