Solved

VBA Code correction to combine three tasks

Posted on 2011-09-06
6
252 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 30

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 30

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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free 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 30

Expert Comment

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

739 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