+ Reply to Thread
Results 1 to 9 of 9

Protection compatibility issue 2002/2003

Hybrid View

Bjornar Protection compatibility... 01-20-2008, 12:03 PM
royUK You should always write the... 01-20-2008, 12:07 PM
royUK I've had a look at your code... 01-20-2008, 12:18 PM
Bjornar I know i should develop on... 01-20-2008, 12:19 PM
Bjornar I know i could clean up the... 01-20-2008, 12:26 PM
Bjornar Roy: I see now that you... 01-20-2008, 12:44 PM
  1. #1
    Forum Contributor
    Join Date
    10-30-2007
    Location
    Norway
    MS-Off Ver
    MS Office 2007
    Posts
    345

    Protection compatibility issue 2002/2003

    I my current project I'm using VBA to change cell comments to different languages depending on the users choice of language. The sheet is protected, and the comments I'm changing is in locked cells. I unprotect the sheet with VBA before I change the language and then protect the sheet after I have changed the comments.

    This works fine in Excel 2003, but when I open the same workbook in Excel 2002 the comments does not change. I think it may be some compatibility issue with the sheet protection

    I have attached a simplified example, and hope someone working on other versions than 2003 can test this, and give me any tip on what I should do to get this to work in Excel 2002. I have not testet my Example file in 2002 yet, but I think the problem should be the same as in my original project.

    Try to change the language selection in cell B2, and see if the comment in cell A2 is changing.

    Here is the code in my example

    Private Sub Worksheet_Change(ByVal Target As Range)
      Dim TextForCellComment As Variant
              
        If Not Intersect(Target, Range("B2")) Is Nothing Then
            
            Worksheets(1).Unprotect
            
            If Range("B2").Value = "English" Then
                TextForCellComment = "This is English text"
             End If
        
            If Range("B2").Value = "Norwegian" Then
                TextForCellComment = "Dette er norsk tekst"
            End If
            
            Worksheets(1).Range("A2").Comment.Text Text:=TextForCellComment
            
            Worksheets(1).Protect
           
        End If
    
    End Sub
    This is crossposted in:
    http://www.mrexcel.com/forum/showthread.php?t=298553
    But no reply yet. Maybe beacuse I could not upload an example file to that forum.
    Attached Files Attached Files

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    You should always write the code in the earliest version that may be used.

    Edit; it translates OK in Excel 2000
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    I've had a look at your code and I think you should declare TextForCellComment as a String, a Variant is slower. This code is a little neater
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim TextForCellComment As String
    
        If Not Intersect(Target, Range("B2")) Is Nothing Then
            With Worksheets(1)
                .Unprotect
                If .Range("B2").Value = "English" Then
                    TextForCellComment = "This is English text"
                Else: TextForCellComment = "Dette er norsk tekst"
                End If
                .Range("A2").Comment.Text Text:=TextForCellComment
                .Protect
            End With
        End If
    End Sub

  4. #4
    Forum Contributor
    Join Date
    10-30-2007
    Location
    Norway
    MS-Off Ver
    MS Office 2007
    Posts
    345
    I know i should develop on the earliest vesion. At my work we have a mix of 2002 and 2003 versions, I work on 2002, but most of the research I do is done on forums like this in my spare time at home. I have 2003 on my laptop at home. This part of the code was written at home, and I discovered that it did not work when I loaded the file at work.

    The problem is that I don't know why it does not work on 2002 ? That's what I want some help to figure out.

  5. #5
    Forum Contributor
    Join Date
    10-30-2007
    Location
    Norway
    MS-Off Ver
    MS Office 2007
    Posts
    345
    I know i could clean up the code, and thanks for the tip.
    This is not my actual code, I just made an simplified example so I should not confuse you with to much code.

    The problem is the Worksheets(1).Unprotect part of the code. In 2002 this is not enough to let me change the cell comments with VBA.

  6. #6
    Forum Contributor
    Join Date
    10-30-2007
    Location
    Norway
    MS-Off Ver
    MS Office 2007
    Posts
    345
    Roy:
    I see now that you edited your reply, and that it worked in version 2000.
    I now attach the full version (not finished yet). Maybe I have overlooked something else that makes it not work in 2002.

    This is the real code:
    Public Sub SetCellComments()
        Dim i, j As Integer
        Dim sDefName As String
        Dim sComText As Variant
        Dim iRowOffset As Integer
        Dim strLng As String
        
              
        Worksheets(1).Unprotect
              
        ' Find current language
        For j = 0 To 5
            If Worksheets(2).Range("c3").Offset(j, 0).Value = Range("Language").Value Then
                iRowOffset = j
            End If
        Next j
            
        Worksheets(1).Activate
        For i = 1 To 23
            sDefName = "Comment" & i
            sComText = Worksheets(2).Range("c12").Offset(iRowOffset, i).Value
            If NameExists(sDefName) Then
                Range(sDefName).Comment.Text Text:=sComText
            End If
            
        Next i
        Worksheets(1).Protect
     
    
    End Sub
    Attached Files Attached Files

  7. #7
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    I think this is causing your problems

    xlPasteValidation

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1