Macro

[RAM]

Hi guys,

Working on a sheet that isn't mine. What does this ws code do?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
If Target.Address <> "$A$69" Then Exit Sub
ActiveSheet.Name = Range("A69").Text
End Sub

Thanks

RAM

 

[acw]

HI

Basically if the text in A69 changes, then the sheet is changed to the value of the cell. It checks to make sure that only 1 cell is selected, that cell does not contain a "blank" and the cell is A69.


Tony

 

[Smitty]

Private Sub Worksheet_Change(ByVal Target As Range) 
   '  If more than one cell is changed (as in copying/pasting multiple cells),
   '  Then do nothing
   If Target.Cells.Count > 1 Then Exit Sub 
   '  If the actioned cell is empty then do nothing (to avoid sheet anming errors)
   If Target = "" Then Exit Sub 
   '  If the actioned cell isn't A69 then don't do anything
   If Target.Address <> "$A$69" Then Exit Sub 
   '  Otherwise change the sheet name to whatever A69 is
   ActiveSheet.Name = Range("A69").Text 
End Sub

Note that it doesn't address invalid WS names and could be more efficient, but it has its uses.

HTH,

Smitty

EDIT: Beaten by the time zones! (Heya Tony!)

 

[RAM]

Thanks guys,

helpfull as always.

RAM

 

[RAM]

Since I have you great coder on lines I could need help to tweak this wb code to be a Workbook_Open code:

Sub ProtectWorkbook()

    Dim wksOne As Worksheet
    Dim strPwd As String
    Dim strSharePwd As String

    Set wksOne = Application.ActiveSheet

    strPwd = InputBox("dw1210")
    strSharePwd = InputBox("dw1210")

    wksOne.ProtectSharing Password:=strPwd, _
        SharingPassword:=strSharePwd

End Sub

And at last, what's the best procedure of protecting the above VBA codes that has protection passwords?

Thanks,

RAM

 

[Tazguy37]

And at last, what's the best procedure of protecting the above VBA codes that has protection passwords?

Thanks,

RAM

Putting your code in an add-in is one of the more secure methods (though by no means foolproof).

 

[RAM]

Thanks for the replay from another great coder on this board. Unfortunately this workbook will be shared with other remote computers and we were planning on using Excel just as it is, with no added toolbars, buttons or, as you suggested, add-ins, but if we do it anyway it must be included in the opening codes and the codes must enforce the use of codes.

Any suggestions?

Thanks for your replay.

RAM

 

[Smitty]

Why not just protect the VBA project? In the VBE, goto Tools-->VBA Project Properties-->Protection-->Lock Project for Viewing-->Add & Verify password.

Once you close the workbook and re-open, the password & protection will be applied.

That'll keep 99% of users honest.

If the worekbook is in Shared mode, then the VBA project is 100% unviewable anyway, until unshared.

Smitty

 

[RAM]

Thanks Smitty for your replay. I believe that is what is needed for now. Let me get busy with it and we'll see what I can get out of it.

RAM