Virtanis,

This is a lot more difficult as that formula could be anywhere on the sheet.
Would it be acceptable to ask the user to select a cell in the code? If so,
you could try this

Public Sub Button1_Click()
Dim oCell
Dim oThis As Worksheet
Set oThis = ActiveSheet
Worksheets("Sheet1").Activate
Set oCell = Application.InputBox("Please select a cell to adjust",
Type:=8)
If Not oCell Is Nothing Then
With oCell
.Value = .Value - 1
End With
End If
oThis.Activate
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Virtanis " wrote in message
...
Yes, and it works fantastically... Thank you very much, I understand
the syntax and I'll remember that.

I'm changing my worksheet around with all kinds of goodies now, but
I've run across one really nasty aspect that I didn't anticipate
before. I'd like to add the same button ( .Value = .Value -1 ) but now
adjacent to a cell that displays the following result.

=VLOOKUP((E2),Widget_Range,3, FALSE)

That seems significantly more difficult when compared to a simple
Sheet1!E3 reference.

:(



JE McGimpsey wrote:
*Note that I assumed you were using a button from the Forms toolbar
and
attaching the macro to it.

In article ,
JE McGimpsey wrote:

one way:

put this in a regular code module:

Public Sub Button1_Click()
With Sheets("Sheet1").Range("E2")
.Value = .Value - 1
End With
End Sub

Note that using the With..End With is equivalent to

Public Sub Button1_Click()
Sheets("Sheet1").Range("E2").Value = _
Sheets("Sheet1").Range("E2").Value - 1
End Sub *


---
Message posted from http://www.ExcelForum.com/