Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #108651

Re: VBA error 1004 on With worksheet.Paste

Show all headers | View raw

This may seem a bit 'long-in-tooth' for you, but here's how I handle 
multi-sheet projects for doing similar tasks...

A:
Each sheet has its own UserInterface (UI) settings stored as a local 
scope defined name. In this case the UI setting for sheet protect is 
named "uiProtect" and its RefersTo is either 0 (zero) or 1 where 1 
indicates the sheet gets protected.

B:
When the workbook opens each sheet gets its uiProtect setting applied 
as follows;

  Protection gets removed...
    wks.Unprotect PWRD
    '//where PWRD is a global scope string constant

  If wks.Names("uiProtect").RefersTo > 0 then 'protection gets reset...
    Call wsProtect(wks.Name)
  End If

The sheet protection is reset when the workbook opens because I make 
use of the *UserInterfaceOnly* option so I don't have to 'toggle' sheet 
protection whenever code makes changes to locked cells. Protection gets 
applied according to Excel version as far as its Options go...


Sub wksProtect(Optional Wks As Worksheet)
' Protects specified sheets according to Excel version.
' Assumes Public Const PWRD as String contains the password, even if 
there isn't one.
'
' Arguments:    Wks  [In]  Optional. Ref to the sheet to be protected.
'                          (Defaults to ActiveSheet if missing)

  If Wks Is Nothing Then Set Wks = ActiveSheet
  On Error Resume Next
  With Wks
    If Val(Application.VERSION) >= 10 Then
      'Copy/paste the desired parameters above the commented line.
      .Protect Password:=PWRD, _
        DrawingObjects:=False, Contents:=True, Scenarios:=True, _
        UserInterfaceOnly:=True, _
        AllowFiltering:=True, _
        AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, _
        AllowFormattingCells:=True ', _
        AllowDeletingColumns:=True, _
        AllowDeletingRows:=True, _
        AllowInsertingColumns:=True, _
        AllowInsertingHyperlinks:=True, _
        AllowInsertingRows:=True, _
        AllowUsingPivotTables:=True
    Else
      .Protect Password:=PWRD, _
        DrawingObjects:=False, Contents:=True, Scenarios:=True, _
        UserInterfaceOnly:=True
    End If
'    .EnableAutoFilter = True
'    .EnableOutlining = True

'    .EnableSelection = xlNoRestrictions
'    .EnableSelection = xlUnlockedCells
    .EnableSelection = xlNoSelection
  End With

End Sub

The important line is the one where the .Protect arguments are set. All 
other 'Allow/Enable' option lines are uncommented as desired. (for v10 
and later, the 'Allow' options need to be placed above the comment 
indicator (see 4th option) as desired.

In your scenario I'd code this task something like this...

Sub MasterTerms_Copy(WksName$, sNames$, sCriteria$)
' Copies data to other sheets and sets sheet controls.
'
' Args:  WksName    The sheetname of the source data.
'        sNames     Comma delimited string of the sheetnames to copy
'                   data to.
'        sCriteria  Comma delimited string of settings to apply to
'                   sheet controls.

  Dim vData, vSettings, ws

  vData = Sheets(WksName).Range("CopyData")
  vSettings = Split(sCriteria, ",")

  For Each ws in Split(sNames, ",")
    With Sheets(ws)
      .Range("CopyData").Resize(LBound(vData), LBound(vData, 2) = vData
      .ComboBox1.ListIndex = CLng(vSettings(0)
      .ComboBox2.ListIndex = CLng(vSettings(1)
      .ComboBox3.ListIndex = CLng(vSettings(2)
      .ComboBox4.ListIndex = CLng(vSettings(3)

      .CheckBox1.Value = CBool(vSettings(4))
      .CheckBox2.Value = CBool(vSettings(5))
      .CheckBox3.Value = CBool(vSettings(6))
      .CheckBox4.Value = CBool(vSettings(7))
      .CheckBox5.Value = CBool(vSettings(8))
      .Range("AfterCopy").Select
    End With 'Sheets(ws)
  Next 'ws
End Sub

..where the source/target ranges are local scope defined names on each 
sheet so I don't have to revise hard-code refs if the range[s] change 
size or location over time for any reason.

HTH

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

VBA error 1004 on  With worksheet.Paste AJ Master <amitmaster@gmail.com> - 2016-03-17 15:36 -0700
  Re: VBA error 1004 on  With worksheet.Paste GS <gs@v.invalid> - 2016-03-18 01:43 -0400
    Re: VBA error 1004 on  With worksheet.Paste GS <gs@v.invalid> - 2016-03-18 01:59 -0400
    Re: VBA error 1004 on  With worksheet.Paste AJ Master <amitmaster@gmail.com> - 2016-03-31 11:25 -0700
      Re: VBA error 1004 on  With worksheet.Paste GS <gs@v.invalid> - 2016-03-31 14:30 -0400

csiph-web