Posts by mkh61083

Re: How to combine these two VBA codes please?

This is the file
Try to change any cell in sheet (1)
any Password (mkh)

Re: How to combine these two VBA codes please?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)Dim cell   As Range
    On Error GoTo enditall
    Application.EnableEvents = False
    For Each cell In Target
        cell = UCase(cell)
        If Target.Cells.Column > 0 Then
            n = Target.Row
            If Excel.Range("C" & n).Value <> "" Then
                Excel.Range("A" & n).Value = Now
                 If ActiveSheet.Name = "Log" Then Exit Sub
                    Application.EnableEvents = False
                    UserName = Environ("USERNAME")
                    NewVal = Target.Value
                    Application.Undo
                    oldVal = Target.Value
                    lr = Sheets("Log").Range("A" & Rows.Count).End(xlUp).Row + 1
                    Sheets("Log").Range("A" & lr) = Now
                    Sheets("Log").Range("B" & lr) = ActiveSheet.Name
                    Sheets("Log").Range("C" & lr) = Target.Address
                    Sheets("Log").Range("D" & lr) = oldVal
                    Sheets("Log").Range("E" & lr) = NewVal
                    Sheets("Log").Range("F" & lr) = UserName
                    Target = NewVal
                    Application.EnableEvents = True
                 End If
              End If
         End If
    Next
enditall:
    Application.EnableEvents = True
End Sub

Re: How to combine these two VBA codes please?

Quote from cytop;773070

Seems straightforward enough. What problems/errors do you get if you just combine them?

In the beginning I'd like to thank you for your Advice in this thread http://www.ozgrid.com/forum/sh…00145&p=772518#post772518
my file show a Run-time error 1004, I think that error refer to the conflict of "Worksheet_Change", so I tried to combine them but my tries were useless.
this is my last step in the file, and I gave up.

I need a help to combine these two VBA codes:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)Dim cell   As Range
  On Error GoTo enditall
 Application.EnableEvents = False
For Each cell In Target
 cell = UCase(cell)
 If Target.Cells.Column > 0 Then
 n = Target.Row
 If Excel.Range("C" & n).Value <> "" Then
 Excel.Range("A" & n).Value = Now
 End If
 End If
 Next
enditall:
 Application.EnableEvents = True
 End Sub

and

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If ActiveSheet.Name = "Log" Then Exit Sub
Application.EnableEvents = False
UserName = Environ("USERNAME")
NewVal = Target.Value
Application.Undo
oldVal = Target.Value
lr = Sheets("Log").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Log").Range("A" & lr) = Now
Sheets("Log").Range("B" & lr) = ActiveSheet.Name
Sheets("Log").Range("C" & lr) = Target.Address
Sheets("Log").Range("D" & lr) = oldVal
Sheets("Log").Range("E" & lr) = NewVal
Sheets("Log").Range("F" & lr) = UserName
Target = NewVal
Application.EnableEvents = True
End Sub

Thanks in Advance

Re: Auto Protect unblank cells, define users permissions and track changes

thanks for your help and thanks for the site, i found other useful topics i can use it in my work, I'll pursue on your Learning method.

Re: Auto Protect unblank cells, define users permissions and track changes

Quote from cytop;772492

My first question would be if you have no experience with VBA why has your Manager given you this work to do? I think he is being unreasonable in the extreme.

This is a help forum - we generally help people with problems. You have not posted a 'problem', you have posted a specification for work to be done. There's a big difference. Your workbook (even if it is saved as an XLSM file) does not contain any code so you are asking someone to start from scratch, build to your requirements, test the completed code and deliver to you. As with all build projects, there will be follow on questions, changes, or maybe something does not work as expected (maybe the 'requirements' were wrong in the first place). Invariably, once something is working, requests for other changes will follow.

This thread, written as it is, should really be posted in the Hire Help forum. This is part of the forum where you offer to pay (the amount is down to you) for work that is outside the scope of the free forums. You will generally get a response within a couple of hours and probably a completed project in a day or 2.

Tell your manager you can get someone to do this for you, quickly, cheaply (Perhaps USD$50 would be fair as requirement #4 is a little involved) and will support it, or make minor changes, for a short period after delivery.

Other than that, this thread is not in the spirit of the free board. Also, there is a policy that each thread deals with 1 issue only so your 5 requirements take it outside that.

However, if you want to start new threads for each of your requirements, you may. You might get lucky... but this is not somewhere to get your work done for free. Sorry.

Display More

Sorry for wasting your time and posting thread in a wrong category.

but about your question "Why my manager given me this work to do?" because i have a very good skills in excel specially formulas, pivot table, power query etc. so he depending on me in many problems and analysis in excel.

thanks anyway, and i think i have to start learning VBA so if you know any channels on YouTube or PDFs may be useful for me to start from the beginning, i will be grateful to you.

sorry for waste your time again

regards

Re: Auto Protect unblank cells, define users permissions and track changes

Quote from cytop;772479

If I can ask - what have you tried to implement these 'requirements'?

Of course you can, these implements were discussed with my manager if it possible to do these 'requirements' in excel and I have not any experience in VBA, So I post a thread here because I know that many of VBA's experts in this forum who can respond to my require.

Hi, i need help,

I have a shared file on a work network, A specific users should be defined to access this file and give everyone of them a password and permissions.

Required:
1- After saving the file the unblank cells automatically locked and can't be edit.
2- If sales man (user_1) write the code in column "C" ,the date of the transaction be filled automatically in column "A" in the same row of the Code.
3- The date that was filled automatically in column "A" should be a value not a formula.
4- Can track changes of the cells.
5- Hide all sheet taps, menu, formula bar and make the window in full screen mode ,but show header of columns and rows.

Thanks in advance.