Need help setting up an object to read NameCode

[tom88Excel]

Hi all,


This is my very first post.... I'm sorry if i'm being too wordy, and please excuse my English as it's my second language...


I'm trying to setup an object to read Namecode but I got stuck.... The following is what I need to do. If you download my spreadsheet at the end of this post, you would know right away what i'm asking without reading the whole thing... Here is my story.


I have a workbook that has many worksheets, all the worksheets are identical. When some items on those sheets are completed, I would click a button which allows me to move that row to another tab named "Completed", Then on the last 3 columns on that row I just moved, I would logged time and date on one column, a NameCode, (example: Sheet2) on another column, and tab Name (example "2nd Tab") on the last column . here is my code.

'When Item on Tab A,B,C,or D is completed, move the item to completed tab
Sub Completed()


'Read Active Sheet Name
currentsheetname = ActiveSheet.Name
currentsheetcode = ActiveSheet.CodeName




'Unprotect Sheets
ActiveSheet.Unprotect
Sheets("Completed").Unprotect




'Move Row to Completed Tab
ActiveCell.EntireRow.Cut
Sheets("Completed").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(0, 3).FormulaR1C1 = Format(Now(), "yyyy/mm/dd hh:nn AM/PM")
ActiveCell.Offset(0, 4).FormulaR1C1 = currentsheetname
ActiveCell.Offset(0, 5).FormulaR1C1 = currentsheetcode
Sheets(currentsheetname).Select
ActiveCell.EntireRow.Delete


'Protect Sheet
Sheets("Completed").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
    AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
    AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True


End Sub

Then on the Completed Tab, i have a button which allows me to move the item back to the Corresponding tabs in case I need to work on the item again... I got stuck in this code... I do not wish to use Tab names (example: "2nd Tab"), because if the tab names get changed, the code will not work. So I want to use Namecode (example: "Sheet2")

'Move Item back to Corresponding Worksheets
Sub Moveback()


'Dim backtosheetcode As Object


backtoSheetname = Range("A" & (ActiveCell.Row)).Offset(0, 4).Text


'Set backtosheetcode = Range("A" & (ActiveCell.Row)).Offset(0, 5).Text




'Unprotect Sheets
ActiveSheet.Unprotect
Sheets(backtoSheetname).Unprotect


'------------------------------------------------------
'My problem is I want to use this code:
'
'backtosheetcode.Unprotect
'
'However, I got stuck, Dim item as Object and set object does not work...
'Can someone please tell me what I'm missing? Thank you.....
'------------------------------------------------------


Range("A" & (ActiveCell.Row)).Select
Range(ActiveCell.Offset(0, 3), ActiveCell.Offset(0, 5)).ClearContents
ActiveCell.EntireRow.Cut
Sheets(backtoSheetname).Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
Selection.Insert Shift:=xlDown




'Protect Sheet
Sheets(backtoSheetname).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
    AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
    AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True




End Sub

I have made an example and here is what the worksheets looks like:
The workbook has 5 tabs, Sheet1 I named "A", Sheet2 "B", Sheet3 "C", Sheet4 "D", "Sheet5 "Completed" and I have a completed button on Tab A to D, when I click on this button it will move the selected row to the tab "Completed", on the "Completed" tab, I have a button which allows me to move the item back to the Corresponding tabs. I uploaded my workbook on my google drive... if you need to see my example

https://drive.google.com/file/d/0B_0BNZd9iC3rZ1ZhOEpHejlLeGc/view?usp=sharing


Please advise if you could help me.. thank you so much

 

[Andrew Poulsom]

Welcome to MrExcel.

Maybe this example will help you:

Sub Test()
    Dim ShCodename As String
    Dim ws As Worksheet
    ShCodename = "Sheet1"
    With ActiveWorkbook
        Set ws = .Worksheets(CStr(.VBProject.VBComponents(ShCodename).Properties("Name")))
    End With
    MsgBox ws.Name
End Sub

Trust access to VBA project must be set.

 

[Kyle123]

I'd prefer:

Function sheetByCodeName(codeName As String) As Worksheet


Dim wkSht As Worksheet


For Each wkSht In ThisWorkbook.Worksheets
    If LCase(wkSht.codeName) = LCase(codeName) Then
        Set sheetByCodeName = wkSht
        Exit Function
    End If
Next wkSht


End Function

Requiring trust to the VBAProject seems a bit strong here

 

[tom88Excel]

Thank you for your help so much Andrew . I put your code in and it reads the Codename and Name without any problem, but when I do this:

Sheets(ws).Unprotect

it gives me a Run-time error'13' Type mismatch

What else am I missing? please help thank you Andrew

 

[tom88Excel]

I'd prefer:

Function sheetByCodeName(codeName As String) As Worksheet


Dim wkSht As Worksheet


For Each wkSht In ThisWorkbook.Worksheets
    If LCase(wkSht.codeName) = LCase(codeName) Then
        Set sheetByCodeName = wkSht
        Exit Function
    End If
Next wkSht


End Function

Requiring trust to the VBAProject seems a bit strong here

Thank you for your help Kyle123 , I'm still trying to understand how to put your code in my workbook... I put the function in and when I run the codes it gives me Compile error: Argument not optional .

 

[tom88Excel]

Thank you for your help so much Andrew . I put your code in and it reads the Codename and Name without any problem, but when I do this:

Sheets(ws).Unprotect

it gives me a Run-time error'13' Type mismatch

What else am I missing? please help thank you Andrew

Oh sorry, I got it now Andrew. it should be ws.unprotect instead... thank you all... i'm very new to vba codes.