Bill Golden
asked on
Help! Can anyone help me add a piece of VBA code to an Excel spreadsheet
I am trying to add the following VBA code to my spreadsheet. I went online and watched a video to add it as a module but it did not work. Spreadsheet is attached!
Private Sub Worksheet_Change(ByVal Target As Range)
Dim s As Variant
If Not Intersect(Target, [T30:T33]) Is Nothing Then
s = Target.Value
If s = "X" Or s = "x" Then
Application.EnableEvents = False
[T30:T33].ClearContents
Target.Value = s
Application.EnableEvents = True
End If
End If
End Sub
JE-Master-WithTabNamingandPageCountB.xls
Private Sub Worksheet_Change(ByVal Target As Range)
Dim s As Variant
If Not Intersect(Target, [T30:T33]) Is Nothing Then
s = Target.Value
If s = "X" Or s = "x" Then
Application.EnableEvents = False
[T30:T33].ClearContents
Target.Value = s
Application.EnableEvents = True
End If
End If
End Sub
JE-Master-WithTabNamingandPageCountB.xls
ASKER
The SETUP sheet
Use this code...
Saurabh...
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("S42,T30:T33")) Is Nothing Then Exit Sub
If Target.Address = "$S$42" Then
If Trim(Range("S42").Value) = "" Or IsDate(Range("S42").Value) = False Then Exit Sub
changesheetnames
Exit Sub
End If
Dim str As String
str = UCase(Target.Value)
If str = "X" Then
Application.EnableEvents = False
Range("T30:T33").ClearContents
Application.EnableEvents = True
End If
End Sub
Saurabh...
ASKER
I still do not know how to add it in VBA
Your excel is acting up on me..Just copy this code and paste in the sheet module of your setup sheet..you go to the vba editor by pressing alt+f11 and then replace the current code with this which you see in the setup sheet.. In the meantime will try to upload your workbook...
ASKER
The routine no longer works. In the sample I received, wherever you put the X (T30, T31, T32 OR T33) the X stayed there. Your modification removes the X once you leave the cell. That will not work!
What you want to do?? You want to leave X in that cell only?? and remove X from rest of the cells??
ASKER
I do not think I know how to answer your question with clarity. I am uploading the original sample I got so you can see how it works.
ASKER
Sorry, left off the file.
EE.xlsb
EE.xlsb
ASKER
That works. However, suddenly any cell on the other sheets to looks for the value of a cell in SETUP...when their is not value in SETUP, the cell returns an 0. That causes that?
ASKER
And, I am now getting a privacy warning that says This documents contains macros, Active X....that cannot be remove by the "Remove personal information from file properties on save" option on the Security tab of the Option's dialog box on the Tools menu.
And, T30...T33 will accept any letter, not only x. Can't will limit the entry to just an X?
And, T30...T33 will accept any letter, not only x. Can't will limit the entry to just an X?
I'm not sure about the first comment as i don't have idea about what you are trying to do..For privacy you can ignore that error...
Also for limiting the entry to X only..Please create a validation over it of X which will do what you are looking for...
Saurabh...
Also for limiting the entry to X only..Please create a validation over it of X which will do what you are looking for...
Saurabh...
ASKER
If you go to one of the sheets besides the SETUP sheet and in B7 enter =SETUP!C1 and B7 suddenly returns a 0, even though it is a General field and the same thing happens if you change it to a Text field.
My firm will not allow me to undo the Privacy Setting and distribute the spreadsheet to bookkeeping personnel.
My firm will not allow me to undo the Privacy Setting and distribute the spreadsheet to bookkeeping personnel.
Here you go use this file i fixed that error..
Also i still don't understand what you mean by the second part..
Saurabh...
JE-Master-WithTabNamingandPageCountB.xls
Also i still don't understand what you mean by the second part..
Saurabh...
JE-Master-WithTabNamingandPageCountB.xls
ASKER
Returning you file now named JE-Master-WithTabNamingand PageCountX .xls
Please look at K4, K5, K6 and K7 on PG1
JE-Master-WithTabNamingandPageCountX.xls
Please look at K4, K5, K6 and K7 on PG1
JE-Master-WithTabNamingandPageCountX.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That did the trick...Thanks a million.
Saurabh...