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
LVL 1
Bill GoldenExecutive Managing MemberAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Quick question for which sheet you want to apply this code??

Saurabh...
0
Bill GoldenExecutive Managing MemberAuthor Commented:
The SETUP sheet
0
Saurabh Singh TeotiaCommented:
Use this code...

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

Open in new window


Saurabh...
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Bill GoldenExecutive Managing MemberAuthor Commented:
I still do not know how to add it in VBA
0
Saurabh Singh TeotiaCommented:
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...
0
Saurabh Singh TeotiaCommented:
Enclosed is your workbook...

Saurabh...
JE-Master.xls
0
Bill GoldenExecutive Managing MemberAuthor Commented:
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!
0
Saurabh Singh TeotiaCommented:
What you want to do?? You want to leave X in that cell only?? and remove X from rest of the cells??
0
Bill GoldenExecutive Managing MemberAuthor Commented:
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.
0
Bill GoldenExecutive Managing MemberAuthor Commented:
Sorry, left off the file.
EE.xlsb
0
Saurabh Singh TeotiaCommented:
Use this file...

Saurabh...
JE-Master.xls
0
Bill GoldenExecutive Managing MemberAuthor Commented:
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?
0
Bill GoldenExecutive Managing MemberAuthor Commented:
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?
0
Saurabh Singh TeotiaCommented:
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...
0
Bill GoldenExecutive Managing MemberAuthor Commented:
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.
0
Saurabh Singh TeotiaCommented:
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
0
Bill GoldenExecutive Managing MemberAuthor Commented:
Returning you file now named JE-Master-WithTabNamingandPageCountX.xls

Please look at K4, K5, K6 and K7 on PG1
JE-Master-WithTabNamingandPageCountX.xls
0
Saurabh Singh TeotiaCommented:
Their you go..changed the formula..Use this...

Saurabh...
JE-Master-WithTabNamingandPageCountX.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill GoldenExecutive Managing MemberAuthor Commented:
That did the trick...Thanks a million.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.