How can I add a value to an existing cell value just by pressing +

[USAMax]

My program is a material cost estimator:

In cells D14 through D38 and D42 through 99 I need to add values using a User Form.

For this example, cell D20 is selected, and it has a value of 100' of drywall. I am adding another room so I want to press + that will bring up my User Form that will ask for the room's width x depth x height. If I enter 10 x 20 x 8 I want 1600 added to the existing 100.

My guess is I need to use the BeforeDoubleClick function, but I am open to suggestions.

Dave

 

[6StringJazzer]

Not sure why you are thinking BeforeDoubleClick when you have not mentioned any double click behavior in your description.

I wasn't sure how much of this you needed us to do for you. This is just to activate your userform. If you don't even have a userform, that is kind of a whole different question but we can pursue it here.

Put this code into the module for the worksheet you want to monitor:

Private Sub Worksheet_Activate()
   If Not Intersect(ActiveCell, Range("D14:D38,D42:D99")) Is Nothing Then
      Application.OnKey "{+}", "ShowForm"
      Application.OnKey "{107}", "ShowForm"
   Else
      Application.OnKey "{+}"
      Application.OnKey "{107}"
   End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Not Intersect(Target, Range("D14:D38,D42:D99")) Is Nothing Then
      Application.OnKey "{+}", "ShowForm"
      Application.OnKey "{107}", "ShowForm"
   Else
      Application.OnKey "{+}"
      Application.OnKey "{107}"
   End If
End Sub

Put this code into a Standard Module (like Module1)

Public Sub ShowForm()
   UserForm1.Show
End Sub

Replace UserForm1 with the name of your actual form.

 

[USAMax]

Jeff, thank you for answering my question so quickly and I apologize for getting back to you, but I have been struggling to get your code to choose between two different user forms.

I am trying to add this code for one UserForm to calculate Linear Feet...
If ActiveCell.Offset(0, 2) = "LFT" Then

And this code to choose the other UserForm to calculate Square Feet...
If UCase(ActiveCell.Offset(0, 2)) = "SQ FT-A" Then

Where should I add this code?
The UserForms are:
AddLinearFoot
AddSquareFootage

 

[6StringJazzer]

I think it would go in the ShowForm sub.

Public Sub ShowForm()
   If ActiveCell.Offset(0, 2) = "LFT" Then
      AddLinearFoot.Show
   ElseIf UCase(ActiveCell.Offset(0, 2)) = "SQ FT-A" Then
      AddSquareFootage.Show   
   End If
End Sub

 

[USAMax]

Hi again Jeff, the function you gave me has been working great until recently. Now when I press "+" nothing happens. I put a break within each of the two worksheet routines but none of the key presses are entering either routine. My code is exactly as you have it above. Any suggestions?

 

[USAMax]

I opened a copy from Aug 11 and it still works. The only thing that I know I changed is that I inserted a column in front of column E.

 

[6StringJazzer]

The Sub I gave you specifically looks for you to select in certain cells of column D. Now that you have inserted a column, are you still using column D for that purpose? If the only thing you changed is inserting a column, that that's where we have to look for the problem.

 

[USAMax]

Thank you Jeff, went back one version and reinserted a column in front of column E and it still works.

I very much appreciate all the help you have provided.

 

[6StringJazzer]

Glad you sorted it. I can't imagine what might have happened between the two versions.