Allow for User Input but keep a formula if no input

[Nivian]

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.

 

[Snakehips]

@Nivian. Something like this maybe....

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]

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....

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]

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

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]

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.

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

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]

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]

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

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]

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.....

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]

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]

@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.

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