Link to home
Start Free TrialLog in
Avatar of Bill Golden
Bill GoldenFlag for United States of America

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
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

Quick question for which sheet you want to apply this code??

Saurabh...
Avatar of Bill Golden

ASKER

The SETUP sheet
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...
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...
Enclosed is your workbook...

Saurabh...
JE-Master.xls
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??
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.
Sorry, left off the file.
EE.xlsb
Use this file...

Saurabh...
JE-Master.xls
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?
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?
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...
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.
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
Returning you file now named JE-Master-WithTabNamingandPageCountX.xls

Please look at K4, K5, K6 and K7 on PG1
JE-Master-WithTabNamingandPageCountX.xls
ASKER CERTIFIED SOLUTION
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That did the trick...Thanks a million.