Update Current Row

[Guest]

Range("B5").Select
Selection = Range("C5")
Range("C5").Select
Selection.ClearContents

Range("C5").Select
Selection = InputBox("Enter This Runs Figures:")
If Response1 = vbYes Then

If Selection = "" Then
MsgBox "Nothing to enter"
Else
MsgBox "you cancelled"
End If
End If

ActiveSheet.Range("D5").Value = ActiveSheet.Range("E5").Value + _
ActiveSheet.Range("C5").Value

I have this code attached to a command button in G5. This works fine. My
problem is that I have more than one row that needs to be like this and i
need to be able to run the code seperately for whatever row i choose. So
there will be a number of command buttons. I need the code to run on the
same row as the command button and nowhere else. Can anyone help?

 

[Guest]

hi,
your main problem i think is that you are using hard references to your row
i.e. Range("D5"). it would be better i think to use generic references using
the activecell with the offset() reference. for example...instead of.....

ActiveSheet.Range("D5").Value = ActiveSheet.Range("E5").Value + _
ActiveSheet.Range("C5").Value

use something like this...

ActiveSheet.ActiveCell.Value = ActiveSheet.Activecell.offset(0,1).Value + _
ActiveSheet.ActiveCell.Offset(0,-1).Value

this way your code would work on any row you click on and you would only
need one command button. the only problem would be making sure you click the
row you wish to update. remember.....computers are stupid. they do exactly
what you tell them.....withour question.
read up on activecell and offset() in VB help.

hope this helped.
regards
FSt1

 

[Don Guillett]

Your sub if full of undesirable selections. And, why won't one macro
assigned to one button work for the activecell.row
Don't use command button

Sub fillinformulas()'assign to button or shape
ar = ActiveCell.Row
Cells(ar, "c") = InputBox("Enter This Runs Figures:")
If Cells(ar, "c") = "" Then MsgBox "oh oh"
Cells(ar, "d") = Cells(ar, "c") + Cells(ar, "e")

End Sub
instead of

Range("B5").Select
Selection = Range("C5")
Range("C5").Select
Selection.ClearContents

Range("C5").Select
Selection = InputBox("Enter This Runs Figures:")
If Response1 = vbYes Then

If Selection = "" Then
MsgBox "Nothing to enter"
Else
MsgBox "you cancelled"
End If
End If

ActiveSheet.Range("D5").Value = ActiveSheet.Range("E5").Value + _
ActiveSheet.Range("C5").Value

 

[Dave Peterson]

First, go into design mode and change the .takefocusonclick property to false
for that commandbutton.

This will keep the activecell active.

After that, try this code in your _click event:

Option Explicit
Private Sub CommandButton1_Click()
Dim myRow As Long
Dim myVal As Variant

'ActiveCell.Select
myRow = ActiveCell.Row

Me.Cells(myRow, "B").Value = Me.Cells(myRow, "C")
With Me.Cells(myRow, "c")
.ClearContents
myVal = InputBox("Enter This Run's Figures:")
If myVal = "" Then
MsgBox "Nothing to enter"
Else
.Value = myVal
End If
End With

Me.Cells(myRow, "D").Value _
= Me.Cells(myRow, "E").Value + Me.Cells(myRow, "C").Value

End Sub