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/

Yeah, I see the problem with the target cells being all over the sheet
:(

My next thought would have been to modify the macro to know th
location of the actual target cell by pulling that cell locatio
(Column/Row) from the current sheet.

Well, after an elaborate series of walkarounds, I was able to pin down
in a cell on my summary page, the variable location of the target cel
that will be the subject of the "-1" adjustments. As I change my data
this cell constantly updates that location.

So now, what I was trying to figure out is something along these line
(forgive the novice pseudo-code).

Public Sub Button3_Click()
LocationXY = Cell D3 on the summary sheet2 'which has the real locatio
of the moving target cell on sheet1
With Sheets("Sheet1").Range("LocationXY")
.Value = .Value - 1
End With
End Sub

Geez... I feel like I'm trying to kill a fly with a shotgun. :)


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

Sorry, I am missing how you know what the target cell will be. What is the
rule?

--

HTH

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

"Virtanis " wrote in message
...

Yeah, I see the problem with the target cells being all over the sheet.
:(

My next thought would have been to modify the macro to know the
location of the actual target cell by pulling that cell location
(Column/Row) from the current sheet.

Well, after an elaborate series of walkarounds, I was able to pin down,
in a cell on my summary page, the variable location of the target cell
that will be the subject of the "-1" adjustments. As I change my data,
this cell constantly updates that location.

So now, what I was trying to figure out is something along these lines
(forgive the novice pseudo-code).

Public Sub Button3_Click()
LocationXY = Cell D3 on the summary sheet2 'which has the real location
of the moving target cell on sheet1
With Sheets("Sheet1").Range("LocationXY")
Value = .Value - 1
End With
End Sub

Geez... I feel like I'm trying to kill a fly with a shotgun. :)



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