Solved

VBA Code correction to combine three tasks

Posted on 2011-09-06
6
247 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
  • 3
  • 3
6 Comments
 
LVL 29

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 29

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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 29

Expert Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now