Posts by Chewy73

Re: Locking worksheet

Just not sure I need this line

Me.EnableSelection = xlNoSelection

Thanks again for pointing me in the right direction

:sing:

Re: Locking worksheet

Thanks Think I have it working now

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$J$83" Then
            Me.Unprotect "Password"
            
    Me.Protect "Password", DrawingObjects:=True, Contents:=True, Scenarios:=True
    Me.EnableSelection = xlNoSelection
    End If
End Sub

Re: Locking worksheet

Thanks for that but it dosent seem to be working, the worksheet has a level of protection on it to start with to prevent formulas being altered so only giving the user acess to certian cells. what I need to achive is when say cell K2 has the word Approved in it then the sheet unlocks and then re-locks but making all cells and object unselectable.. Not sure if that makes sense

I want to lock the worksheet when text appears in a specific cell,
kind of like a lock out once final approval has been made to prevent any other user altering the appoved ammout.
Can anyone help

Re: Want Macro to work with template hidden?

Thanks for that

Re: Want this code to ignore several pages

Thanks Dave, in a mad dash to get this fixed...

Hi quick question about the code below. the code was posted by Barry (thanks it works great) I would like the code to ignore 6 specific pages is this possible and if so can the code be written in such away that it can be used in other actions

Matt
iwrk4dedpr
[LIST]http://www.ozgrid.com/forum/showthread.php?t=23887

'   Local Variables
    Dim wks                 As Worksheet
    Dim rngLinkCell         As Range
    Dim strSubAddress       As String, strDisplayText       As String
     
     '   Step 1 : Loop through all worksheets
     ' 1a : Clear all current hyperlinks
    Worksheets("Summary").Range("B9:B65536").ClearContents
     ' 1b : Create Linked index list
    For Each wks In ActiveWorkbook.Worksheets
        Set rngLinkCell = Worksheets("Summary").Range("B65536").End(xlUp)
        If Worksheets("Summary").Range("B9") = "" Then
            Set rngLinkCell = Worksheets("Summary").Range("B9")
            End If
            If rngLinkCell <> "" Then Set rngLinkCell = rngLinkCell.Offset(1, 0)
            strSubAddress = "'" & wks.Name & "'!D7"
            strDisplayText = "" & wks.Name
            Worksheets("Summary").Hyperlinks.Add Anchor:=rngLinkCell, Address:="", SubAddress:=strSubAddress, TextToDisplay:=strDisplayText
        Next wks

Hi I am looking to insert a line int this code so that I can hide the Template sheet rarther than having to call it "Template Do Not Use" please could somebody advise brand new to Macros but seem to be doing ok just glad I found this forum...

Matt

[/SIZE]Public Sub SheetCopy()Dim Sh As Worksheet, TemplateSh As WorksheetDim ShNum As Integer, HighestNum As IntegerDim SheetCoreName As StringSheetCoreName = "REQ - "Set TemplateSh = Sheets("Template Do Not Use")For Each Sh In WorksheetsIf InStr(1, Sh.Name, SheetCoreName) = 1 ThenShNum = Val(Right(Sh.Name, Len(Sh.Name) - Len(SheetCoreName)))If ShNum > HighestNum Then HighestNum = ShNumEnd IfNext ShTemplateSh.Copy after:=Sheets(Sheets.Count)ActiveSheet.Visible = xlSheetVisibleActiveSheet.Name = SheetCoreName & HighestNum + 1End Sub[size=14]

[/SIZE]

Re: Macro for creating hyperlink

how can you specify the start cell for the above macro as it does everything I want except I need it to start populating the worksheet from B15