Solved

VBA Code correction to combine three tasks

Posted on 2011-09-06
6
253 Views
Last Modified: 2012-05-12
Dear Experts,
recently I asked three questions which were all solved with vba code and each solution works fine. Now I need to combine all three solution into one but my coding experience is not good enough.
See here:
http://www.experts-exchange.com/Microsoft/Applications/Q_27286592.html
http://www.experts-exchange.com/Microsoft/Applications/Q_27288516.html
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27159284.html

The code below is almost working :-)
1. The tracking does not fix the previous value only the new one in the new line
2. I want to secure the history sheet with a password to prevent any changes from users. At the same time code must work and be able to write values to that sheet..

thanks
Nils




Private Sub Worksheet_Change(ByVal Target As Range)
Dim vOld
Dim rng As Range
Set rng = Range("A5:AA100")

    If Not Intersect(Target, rng) Is Nothing Then
    Debug.Print (Target.Column)
        Target.Offset(0, 29 - Target.Column).Value = Now()
        Target.Offset(0, 30 - Target.Column).Value = Environ("UserName")
    vOld = Target
    End If

With Sheets("Tracking").Range("A" & Rows.Count).End(xlUp)(2)
    .Value = Now
    .Offset(, 1).Value = Environ("username")
    .Offset(, 2).Value = Target.Address
    .Offset(, 3).Value = vOld
    .Offset(, 4).Value = Target
End With

End Sub


Private Sub Workbook_Open()
    Sheets("Tracking").Protect Password:="123456", DrawingObjects:=True, _
        contents:=True, Scenarios:=True, _
        userinterfaceonly:=True
    Sheets("COA-Matrix")EnableAutoFilter = True
End Sub


0
Comment
Question by:Petersburg1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 31

Accepted Solution

by:
gowflow earned 250 total points
ID: 36488132
replace this
Sheets("COA-Matrix")EnableAutoFilter = True
by this
Sheets("COA-Matrix").EnableAutoFilter = True

Your missing the point "." before EnableAutoFilter

What else is not working ?
gowflow
0
 

Author Comment

by:Petersburg1
ID: 36488174
I want to use  the the same code for 3 excel files which are located in three different folders, however when I adapt the ranges in the excel files the code for all excel files is changes?
The code is currently saved in "sheet1". Looks like I do not know the architecture of where to save..
thanks for advise.
Where to save so I can adapt ranges and save together with each excel file?
thanks
0
 
LVL 31

Expert Comment

by:gowflow
ID: 36488241
You can save them in a module but then it depends how they are called. Can't you post them I will fix it for you ? easier for both of us
gowflow
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:Petersburg1
ID: 36489230
looks like I have fixed it.
thanks for help anyway
0
 

Author Closing Comment

by:Petersburg1
ID: 36489237
thanks
0
 
LVL 31

Expert Comment

by:gowflow
ID: 36489290
tks for the grade
gowflow
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question