PDA

View Full Version : change the (Worksheet_Change) event for a module (Sub).



marreco
06-06-2012, 04:28 PM
Hi.
I need to change the code Change event for a module. If someone could help me, I'll be very grateful!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewValue As Variant, OldValue As Variant
If Target.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("A18:I90")) Is Nothing Then
NewValue = Target.Value
Application.EnableEvents = False
Application.Undo
OldValue = Target.Value
If OldValue = "" Then
Target.Value = NewValue
ElseIf InputBox("Enter your password") = "pwr" Then
Target.Value = NewValue
Else: MsgBox "You do not have permission to change cells.", 16, "Células Bloqueadas"
Target.Value = OldValue

End If
Application.EnableEvents = True
End If
End Sub

defcon_3
06-06-2012, 07:45 PM
Hmm sorry I didnt get what you mean?
Can you explain further?

marreco
06-06-2012, 07:56 PM
need to transfer to a module. Sub test () here will be the code end sub

mikerickson
06-06-2012, 08:08 PM
And what do you want sub Test to do?

defcon_3
06-06-2012, 08:52 PM
Im not certain but I think moving it to a module will cause it(macro) not to work on any changes you made in worksheet otherwise your sub is being called.

Opv
06-06-2012, 08:55 PM
Im not certain but I think moving it to a module will cause it(macro) not to work on any changes you made in worksheet otherwise your sub is being called.
If you just want to temporarily disable the sub, why not just comment the sub out rather than move it?

Jan Karel Pieterse
06-06-2012, 10:30 PM
Under which conditions do you want the change event not to process its code?

mikerickson
06-06-2012, 10:32 PM
I'm concerned about Application.Undo
OldValue = Target.Value being used to "store" the previous value when the routine is called by the user rather than by the Change event.

marreco
06-07-2012, 03:29 AM
I need to run this code for a in Commandbutton
Thank you!!

Aussiebear
06-07-2012, 03:34 AM
Then simply use the command button name_click event to activate the code

marreco
06-07-2012, 03:35 AM
Give me an example because I did not understand.

marreco
06-07-2012, 10:08 AM
hello, anyone could understand what I tried to explain?:doh:

Sub test ()
here will be the code
end sub

Would work well?
Private Sub CommandButton1_Click()
Dim NewValue As Variant, OldValue As Variant
If Target.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("A18:I90")) Is Nothing Then
NewValue = Target.Value
Application.EnableEvents = False
Application.Undo
OldValue = Target.Value
If OldValue = "" Then
Target.Value = NewValue
ElseIf InputBox("Enter your password") = "pwr" Then
Target.Value = NewValue
Else: MsgBox "You do not have permission to change cells.", 16, "Células Bloqueadas"
Target.Value = OldValue

End If
Application.EnableEvents = True
End If
End Sub

Cross-Post
http://www.excelforum.com/excel-programming/836447-change-the-worksheet_change-event-for-a-module-sub.html?p=2815824#post2815824

Jan Karel Pieterse
06-08-2012, 02:50 AM
Marecco: The button click event you propose will not work, as "Target" is unknown.

marreco
06-08-2012, 04:43 AM
Hi.
I know, that's why I need your help to change the code so that it works in a CommandButton. Thank you!

Jan Karel Pieterse
06-08-2012, 05:55 AM
Does it do what you want if you replace all "Target" with "ActiveCell"?

marreco
06-08-2012, 10:36 AM
This code has the function to block the change data in a spreadsheet.
what I want is a code that does it!
However, it has to be within a module and not Change Event!

My intention is after being executdo a routine, just after all the cells in that range will be blocked.

Thank you!!

Jan Karel Pieterse
06-10-2012, 09:21 PM
I fail to understand what you are trying to achieve. If you want the code to respond to a change, the change event is where the code should go.
Please try to describe as clearly as you can what should happen and when.

marreco
06-11-2012, 04:29 AM
I need a code after the user types in any cell of the range "A18: I90", which once used the cells can no longer be edited (data type other cells). I have a code of cahmdo Macro_1, and I want to be executed after Macro_1, call the the code to not allow editing cells.
Sub MAcro_1

My code

Call not_edit
End sub

Jan Karel Pieterse
06-11-2012, 06:15 AM
There are multiple ways to prevent data entry in cells.
The simplest is by unlocking cells in which data entry *is* allowed and then protecting the worksheet with a password.

After doing so, you could protect the cells from data entry like so:

With WorkSheets("Sheet1")
.Unprotect "Password"
.Range("A18:I19").Locked = True
.Protect "Password"
End With

marreco
06-15-2012, 03:32 AM
Hi.
this has solved my problem thanks!