Creating a patch in vba

Feester

New Member
Joined
May 21, 2007
Messages
26
I have previously created a patch (with help from this forum) that allows you to choose a spreadsheet to open and then change details in the spreadsheet, save the changes and then close the spreadsheet.

I am trying to use a similar patch and I can't get it to work. I can get the spreadsheet to open but I can't get the change to take place. What I want it to do is open the spreadsheet and then go to the tab called Summary Form. When it is in that tab I need it to unprotect it. It then has to look at each cell between Column G19 and G40 and if there is a 0 in that cell then do nothing. If there is a number in there then I want it to unhide a tab. I am using a similar code from the original spreadsheet where it works fine but it doesn't seem to like being transported into this patch. Can anyone see a way for me to make this work?

Modified Code and original code:

Original Code:

Private Sub Worksheet_Change(ByVal Target As Range)
'Set sheet tab to hide
Dim i As Integer
Dim Sheetname As String
Dim Sheetname2 As String
Dim SheetLen As Integer
Dim SheetLen2 As Integer
If Intersect(Target, Range("$G$19:$G$39")) Is Nothing Then Exit Sub
Sheetname = Target.Offset(0, 9).Value
SheetLen = Len(Sheetname)
Sheetname2 = Target.Offset(0, 10).Value
SheetLen2 = Len(Sheetname2)
For i = 3 To Worksheets.Count
If Left(Sheets(i).Name, SheetLen) = Sheetname Then
Sheets(i).Visible = False
ElseIf Left(Sheets(i).Name, SheetLen2) = Sheetname2 Then
Sheets(i).Visible = True
End If
Next i
End Sub


Modified Code:

Private Sub Workbook_Open()
' Get the appropriate file information
Dim sFileName As String
sFileName = Application.GetOpenFilename
' They have cancelled.
If sFileName = "False" Then Exit Sub
Workbooks.Open Filename:=sFileName
' Unprotects the workbook
ActiveWorkbook.Sheets("Summary Form").Unprotect ("test1")
'Set sheet tab to hide
Dim i As Integer
Dim a As Integer
Dim Sheetname As String
Dim Sheetname2 As String
Dim SheetLen As Integer
Dim SheetLen2 As Integer
For a = 19 To 40
If Intersect(Target, Cells(a, 7)) Is Nothing Then
Exit For
End If
Next a
Sheetname = Target.Offset(0, 9).Value
SheetLen = Len(Sheetname)
Sheetname2 = Target.Offset(0, 10).Value
SheetLen2 = Len(Sheetname2)
For i = 3 To Worksheets.Count
If Left(Sheets(i).Name, SheetLen) = Sheetname Then
Sheets(i).Visible = False
ElseIf Left(Sheets(i).Name, SheetLen2) = Sheetname2 Then
Sheets(i).Visible = True
End If
Next i
ActiveWorkbook.Sheets("Summary Form").Protect ("test1")
' Save & Close the updated workbook
ActiveWorkbook.Close True
' Close the update workbook without saving
ActiveWorkbook.Close False
End Sub

Any help would be greatly appreciated.

Thanks
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Watch MrExcel Video

Forum statistics

Threads
1,101,805
Messages
5,482,994
Members
407,371
Latest member
Ernest F Mink

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top