Edit Current Selected Cell

[LordDragon]

Greetings,


I'm trying to add an edit function to my workbook. This is to allow the user to edit the Part Number on a list if the part they want for that system is wrong. There are multiple sheets with different amounts of data, but all these sheets are pulling their information from a "Master DataList" page.

So what I need to do is provide the user an InputBox so they can edit the current selected cell. That cell can be any currently used cell in Column "C" on the worksheet, but the number of rows that covers will vary.

I'm looking for samples, but not having much luck so far.

Thanks.

[LordDragon]

Ok. I've come up with the following code (which doesn't work) as an example of what I'm trying to do.


Sub EditSelection(control As IRibbonControl)

Dim partInput As String


With ActiveSheet
If ActiveCell.Range = "C" Then
ActiveCell.Select
partInput = InputBox("Please enter the part number you want.", "Part Number Change")
ActiveCell.FormulaR1C1 = partInput
End If
End Sub


So the basic concept here is to make sure the selected cell is in Column C and within the range of use cells. (I'll be modifying the code slightly for the control that will let the user add new parts.) Then to pull up an Input Box they will use to enter the new part number.

If possible, I would like the Input Box to populate with the current value of the selected cell, and/or of the Input Box is blank and they select "Ok" instead of "Cancel" then it doesn't change the cell value.

Any help is appreciated.

[Paul_Hossler]

Assuming that this is a callback to a CustomUI button

Not tested, but maybe something along these lines




Sub EditSelection(control As IRibbonControl)
Dim partInput As String

With ActiveSheet
If Intersect(ActiveCell, .Columns(3)) Is Nothing Then Exit Sub
If Intersect(ActiveCell, .UsedRange) Is Nothing Then Exit Sub

partInput = InputBox("Please enter the part number you want.", "Part Number Change")

If Len(partInput) = 0 The Exit Sub

ActiveCell.Value = partInput

End Sub

[LordDragon]

There was a small typo here "If Len(partInput) = 0 The Exit Sub" (should be Then Exit Sub).

And I had to add "End With" before End Sub.

Otherwise this works great.

Thanks a lot. I don't know why I couldn't figure this out.