Allow for User Input but keep a formula if no input

Nivian

New Member
Joined
Jun 8, 2018
Messages
10
Good day

Need help with VBA. If cell is blank a formula must be active in the cell, so for example A1 is blank however if I put a value in cell A2 then an activate formular like =A2*10 should be in A1 so I get result. However if i want to type my own value in A1 I can without affecting the formula should I need to.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,161
Office Version
  1. 2013
Platform
  1. Windows
@Nivian. Something like this maybe....

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub

Application.EnableEvents = False
If Target = "" Then Target.Formula = "=A2*10"
Application.EnableEvents = True

End Sub

Hope that helps.
 

Nivian

New Member
Joined
Jun 8, 2018
Messages
10
Good evening Snakechips

Wows that worked great. However that works fine in a blank sheet. When trying to use it in my form it doesn't get the same results. =INDEX(ASSETDB[Inventory number],MATCH(D29,ASSETDB[Asset],0)) that is the formula to lookup the serial number then return the inventory number. The return must be in cell d27 and d29 is the number to match in the DB.

Any advice?

@Nivian. Something like this maybe....

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub

Application.EnableEvents = False
If Target = "" Then Target.Formula = "=A2*10"
Application.EnableEvents = True

End Sub

Hope that helps.
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,161
Office Version
  1. 2013
Platform
  1. Windows
Like below then???????????

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("D27")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub

Application.EnableEvents = False
If Target = "" Then Target.Formula = "=INDEX(ASSETDB[Inventory number],MATCH(D29,ASSETDB[Asset],0))"
Application.EnableEvents = True

End Sub
 

Nivian

New Member
Joined
Jun 8, 2018
Messages
10

ADVERTISEMENT

Hi Snakechips

Please see pictures. Maybe you can spot what I am doing wrong. I have the formula in M9 and it returns the barcode. In sheet 5 I have the first code you gave me and when the cell is selected the formula is visible. However when I use the code for sheet 2 and then I select D27 I do not see the formula.

1.PNG
2.PNG
3.PNG




Like below then???????????

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("D27")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub

Application.EnableEvents = False
If Target = "" Then Target.Formula = "=INDEX(ASSETDB[Inventory number],MATCH(D29,ASSETDB[Asset],0))"
Application.EnableEvents = True

End Sub
 

Nivian

New Member
Joined
Jun 8, 2018
Messages
10
Good day Snakechips

Thank you very much for taking the time to help. Still does not seem to be working. Will upload a link to the document maybe you can help then.
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,161
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Good Morning Nivian
Looking at that image, the problem is that you are actually working with merged cells.
Try this.....

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("D27")) Is Nothing Then Exit Sub
If Not Target.Cells.Count = 14 And Not Target.Cells.Count = 1 Then Exit Sub

Application.EnableEvents = False

If Range("D27") = "" Then Range("D27").Formula = "=INDEX(ASSETDB[Inventory number],MATCH(D29,ASSETDB[Asset],0))"

Application.EnableEvents = True

End Sub
 

Nivian

New Member
Joined
Jun 8, 2018
Messages
10
Good day Snakechips

Awesome, just have to select D27 and press delete and it works. Thank you very much. Can you point in me the direction of some resources you recommend to start learning more about excel and VBA please, it would be greatly appreciated.


Good Morning Nivian
Looking at that image, the problem is that you are actually working with merged cells.
Try this.....

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("D27")) Is Nothing Then Exit Sub
If Not Target.Cells.Count = 14 And Not Target.Cells.Count = 1 Then Exit Sub

Application.EnableEvents = False

If Range("D27") = "" Then Range("D27").Formula = "=INDEX(ASSETDB[Inventory number],MATCH(D29,ASSETDB[Asset],0))"

Application.EnableEvents = True

End Sub
 

Nivian

New Member
Joined
Jun 8, 2018
Messages
10
Apologies

So excited forgot to ask

If i wanted to repeat this for cell D29 would I just copy and paste this code below its self and change the cell ref from D27 to D29. I would like it that if the user either has the Inventory number D27 or the Asset number D29 they could enter either and the other cell will bring up the info.

Good day Snakechips

Awesome, just have to select D27 and press delete and it works. Thank you very much. Can you point in me the direction of some resources you recommend to start learning more about excel and VBA please, it would be greatly appreciated.
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,161
Office Version
  1. 2013
Platform
  1. Windows
@Nivian
You can only have one instance of each type of Worksheet Event code in any one sheet.
So, if you want multiple/optional actions they all need to be handled by the logic within your code.

It's partially guesswork on my part but I imagine that something like the below is what you are wanting?
Rather than put a 'hard' formula in the cell that is complementary to the changed (Target) cell, I have used the vba Evaluate function to generate the value.
Replace the previous code with this and see how you get on.


VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'If changed cell does not intersect with cells of interest then do nothing
If Intersect(Target, Range("D27, D29")) Is Nothing Then Exit Sub
'If more cells selected than represents a cell of interest then do nothing
If Not Target.Cells.Count = 14 And Not Target.Cells.Count = 1 Then Exit Sub
'assume selection is row 27
r = 27
' check and change r if  rowis 29
If Intersect(Target, Range("D27")) Is Nothing Then r = 29
'Disable the triggering of Events so that changes made by this code don't create an infinite loop of this code
Application.EnableEvents = False

'do appropriate lookup for the complimentary value

    If r = 27 Then
        Range("D29").Value = WorksheetFunction.IfError(Evaluate("=INDEX(ASSETDB[Asset],MATCH(D27,ASSETDB[Inventory number],0))"), "Not Found").   '??? Edit formulas to suit
        Else
        Range("D27").Value = WorksheetFunction.IfError(Evaluate("=INDEX(ASSETDB[Inventory number],MATCH(D29,ASSETDB[Asset],0))"), "Not Found").  '????
   
    End If
   
'***  Enable the triggering of Event Code
Application.EnableEvents = True

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,752
Messages
5,638,157
Members
417,011
Latest member
Amaden95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top