Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Update Current Row
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Update Current Row
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 "enyaw" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Update Current Row
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 -- Don Guillett SalesAid Software "enyaw" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Update Current Row
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 enyaw wrote: 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? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
update Totals using current date | Excel Discussion (Misc queries) | |||
How to auto update a cell with a current date | Excel Discussion (Misc queries) | |||
Automatically update charts for only the most current 12 months? | Charts and Charting in Excel | |||
Update current time automatically? | Excel Discussion (Misc queries) | |||
Update Links to current Directory?? | Excel Discussion (Misc queries) |