How can i increment and decrament a cell with a touch screen?

[USAMax]

I have a laptop/tablet computer that I use in the field to collect data. Most of the time I am just adding or subtracting one from the current cell.

All of the cells I need to change are in column D from row 14 through row 97.

All of the cells are locked except for columns D and H.

I could build a userform but I am hoping for a more eloquent way.

My hope is to:
1) Increment the cell in column D by touching the cell in column G on the same row.
2) Decrement the cell in column D by touching the cell in column F on the same row.
3) I would like to make these changes without changing the position of the active cell.

Everyone at Mr Excel has been amazing and I know if I do not get an answer, there isn't one.

My thanks in advance,

 

[taigovinda]

Hi Dave, try this:

Option Explicit
Public rngLastSelection As Range


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


On Error GoTo Cleanup


If rngLastSelection Is Nothing Then Set rngLastSelection = Me.Range("D14")


    If Intersect(Me.Range("F14:G97"), Target) Is Nothing Or Target.Cells.Count > 1 Then
        'keep track of cells that are selected so you can go back to them after adjusting D value
        Set rngLastSelection = Target
        
    Else 'if you are incrementing/decrementing D then you don't want F/G to be recorded as having been selected
        With Me.Range("D" & Target.Row)
            Select Case Target.Column
                Case 6
                    .Value = .Value - 1 'F
                Case 7
                    .Value = .Value + 1 'G
            End Select
        End With
        
        Application.EnableEvents = False 'prevent infinite loop, prevent thinking you want F or G to be known as previous active cell
        rngLastSelection.Select
        Application.EnableEvents = True 'events need to be turned on for anything to ever work
    End If


Exit Sub
Cleanup: Application.EnableEvents = True 'events need to be turned on for anything to ever work


End Sub

Tai

 

[USAMax]

Tai,

Simply amazing, you came up with a technique I never used and it did almost everything I wanted. After a few minor changes this is perfect.

I am so happy and so much appreciate you and the time you put into this.

Thank you and happy Thanksgiving to you and your family!

 

[taigovinda]

Thanks for the generous comments Dave. When I saw you were from Chicagoland and started MrExcel within a year of me, of course I had to try my best to make an answer! Happy Tday - and happy black Friday - to your and yours as well.

Tai

 

[USAMax]

Can I change Images and affect their aspect ratio within a UserForm.

I am estimating home repairs and I need a way to choose an image for the type of window I want to use. Once the image is chosen I will use the image description to update the bill of material.

I also want to effect the aspect ration because I meant to order a 5' 6" foot wide by 5' high window and the numbers got reversed so I hope to affect the appearance of the image using the dimensions. Don't know if this is even possible.

Thanks in advance Mr Excel family!