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.