Change the cells value with input box of another cells after people changed a cells

holycome99

New Member
Joined
Nov 16, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Want i gonna to do is that when the people changed the cells, they will be asked for input
and right in the offset(7,1) of this cell, the cell will show the input values.
However, when i use this code, it just can't run and crash after few mins.
Please help.








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

Dim rng As Range
Dim CARLBURN As String



Dim Msg As String

Msg = "What is the CARLORIES BURN for this activity?"
Set rng = Range("B5:B11")


If Not Intersect(Target, rng) Is Nothing Then



InputArea:

CARLBURN = InputBox(Msg, "CARLBURN Input")

    If CARLBURN = "" Then
    MsgBox "Please enter the CARLBURN for this activity!"
    
    Exit Sub
    
    
    
    End If
    If IsNumeric(CARLBURN) = False Then
    MsgBox "Please enter only numerical number!"
    Exit Sub
    
    
    End If
    
    

End If

    
 


Sheets("Other Activities").Select

 Target.Offset(10, 1).Value = CARLBURN
 

 

 
End Sub
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Dermot

Board Regular
Joined
Aug 11, 2006
Messages
85
Office Version
  1. 365
Platform
  1. Windows
Probable cause is that when your code puts the Carlburn value in a cell, it triggers the Change event again. You need to stop that happening.

So try this

VBA Code:
Application.EnableEvents = False
Target.Offset(10, 1).Value = CARLBURN
Application.EnableEvents = True
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,651
Office Version
  1. 2013
Platform
  1. Windows
Try this:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  12/5/2020  1:17:27 AM  EST
If Not Intersect(Target, Range("B5:B11")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim CARLBURN As String
Dim Msg As String
Msg = "What is the CARLORIES BURN for this activity?"
CARLBURN = InputBox(Msg, "CARLBURN Input")
    If CARLBURN = "" Then
    MsgBox "Please enter the CARLBURN for this activity!"
    CARLBURN = InputBox(Msg, "CARLBURN Input")
    If CARLBURN = "" Then: MsgBox "You failed to enter a value for a second time. I will stop the script": Exit Sub
End If
Target.Offset(10, 1).Value = CARLBURN
End If
End Sub
 
Solution

Dermot

Board Regular
Joined
Aug 11, 2006
Messages
85
Office Version
  1. 365
Platform
  1. Windows
Try this:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  12/5/2020  1:17:27 AM  EST
If Not Intersect(Target, Range("B5:B11")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim CARLBURN As String
Dim Msg As String
Msg = "What is the CARLORIES BURN for this activity?"
CARLBURN = InputBox(Msg, "CARLBURN Input")
    If CARLBURN = "" Then
    MsgBox "Please enter the CARLBURN for this activity!"
    CARLBURN = InputBox(Msg, "CARLBURN Input")
    If CARLBURN = "" Then: MsgBox "You failed to enter a value for a second time. I will stop the script": Exit Sub
End If
Target.Offset(10, 1).Value = CARLBURN
End If
End Sub
but that will still trigger the change event when the cell is updated, causing this sub to recurse itself...............surely that is the problem
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,651
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

I'm curious why you wanted to Offset by 10

but that will still trigger the change event when the cell is updated, causing this sub to recurse itself...............surely that is the problem
I test all my scripts. The value entered will be outside the Target range. So it does not cause a problem.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,651
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

I see several questions about your script but I gave a answer you may want to try.
Like if your in one sheet but then want to activate another sheet.
Like this line of code:
Sheets("Other Activities").Select
Did you want the results to be put in another sheet.
 

holycome99

New Member
Joined
Nov 16, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
I see several questions about your script but I gave a answer you may want to try.
Like if your in one sheet but then want to activate another sheet.
Like this line of code:
Sheets("Other Activities").Select
Did you want the results to be put in another sheet.
Works perfectly!
Thank you!!! <3
 

Watch MrExcel Video

Forum statistics

Threads
1,132,912
Messages
5,655,917
Members
418,253
Latest member
TheJackal26

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