Macro Button To Modify A Cell's Contents?
 

I'm just starting to look into VBA to help me do things with my excel
project, but for the most part, I'm a beginner and totally stumped.

Here is my problem.

On Sheet2 I have a whole range of figures and numbers displayed which
are pulled from a variety of other pages (say =Sheet1!E2 as an
example). This is an overview page mostly.

What I really need is to make a macro button next to a display field on
sheet2 that, when pushed, will modify the the actual contents of the
Sheet1!E2 cell by -1. Each push of that button will minus 1 from that
cell indefinitely.

I'm such a programming novice, I hope someone could take a moment of
time to explain how to do this. Thank you very much.


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

Macro Button To Modify A Cell's Contents?
 

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


In article ,
Virtanis wrote:

I'm just starting to look into VBA to help me do things with my excel
project, but for the most part, I'm a beginner and totally stumped.

Here is my problem.

On Sheet2 I have a whole range of figures and numbers displayed which
are pulled from a variety of other pages (say =Sheet1!E2 as an
example). This is an overview page mostly.

What I really need is to make a macro button next to a display field on
sheet2 that, when pushed, will modify the the actual contents of the
Sheet1!E2 cell by -1. Each push of that button will minus 1 from that
cell indefinitely.

I'm such a programming novice, I hope someone could take a moment of
time to explain how to do this. Thank you very much.


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

Macro Button To Modify A Cell's Contents?
 

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

Macro Button To Modify A Cell's Contents?
 

Yes, and it works fantastically... Thank you very much, I understan
the syntax and I'll remember that.

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

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

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

:(



JE McGimpsey wrote:
*Note that I assumed you were using a button from the Forms toolba
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

Macro Button To Modify A Cell's Contents?
 

Virtanis

Here is one way to reduce the selected cells

Sub ReduceByOne()
' reduces values of formulas by 1
' and replaces formula by value
Dim c
For Each c In Selection
c.Value = c - 1
Next c
End Sub

But this converts any formulas you have to a value so you
lose the formula and the link to the other sheet.

To get over this you will have to import the information
by another Macro. And the following gives you an idea.

Sub getValues()
Dim rng As Range
Set rng = Range("B2:C6")
rng = Sheets("Sheet2").Range("B2:C6").Value
End Sub

You can modify these macros and get the button from View,
Toolbars, Forms and choose a Command button then draw it
on your sheet. Right-click to assign macro and then it is
operational.

Remember to format the command button (right-click,
format) and make sure that the button does not print.

Regards
Peter
-----Original Message-----
I'm just starting to look into VBA to help me do things
with my excel
project, but for the most part, I'm a beginner and
totally stumped.

Here is my problem.

On Sheet2 I have a whole range of figures and numbers
displayed which
are pulled from a variety of other pages (say =Sheet1!E2
as an
example). This is an overview page mostly.

What I really need is to make a macro button next to a
display field on
sheet2 that, when pushed, will modify the the actual
contents of the
Sheet1!E2 cell by -1. Each push of that button will
minus 1 from that
cell indefinitely.

Macro Button To Modify A Cell's Contents?
 

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/

Macro Button To Modify A Cell's Contents?
 

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

Macro Button To Modify A Cell's Contents?
 

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/

Macro Button To Modify A Cell's Contents?
 

Well, the display sheet (sheet2) that I'm working on displays th
variable information from found on sheet1 using the choices made by tw
dropdown boxes.

The static fields on the sheet use VLOOKUP based on those two dropdow
menus to find the information on sheet1 and simply displays that resul
on the page. The good thing about the display on this sheet is that
the only real question as to where the target cell is located is th
row number, not the Column (as widgets will always display in th
widgets field, for example).

So, what I did was reversed the entire process: I made some simpl
formulas that converted what was resulted by the dropdown menus into
through DD and +1 to +X. After that, it was a simple "&" combination.
Then, based on the location on the display sheet, it will result "D14
for example; and that is the cell source on Sheet1 where the looku
function is pulling it's data from.

Now all I need to figure out is how to pull that location from sheet
into the formula in the VBA macro so it can then know which cell o
sheet1 to apply the "-1" to. :)

Crude, but it does work.

Bob Phillips wrote:
Sorry, I am missing how you know what the target cell will be. Wha
is the
rule?

--

HTH

Bob Phillips

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

Macro Button To Modify A Cell's Contents?
 

Was that a bit too confusing? :)


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

Macro Button To Modify A Cell's Contents?
 

Well, I finally figured it out.

Since I was able to reverse the process and find the location of th
actual target (Column/Row - stored in H6), I was able to piece thi
little macro together.

Sub Button6_Click()
Dim Spot As Variant
Spot = Range("H6").Value
With Sheets("Sheet1").Range(Spot)
.Value = .Value - 1
End With
End Sub

Thanks for all your help folks... I'm pretty proud of myself as
total VB noob. :) :cool:


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