excel - Copying existing password protected sheet to new workbook as an unprotected sheet does not make the new worksheet unprotected -


copying existing password protected sheet new workbook unprotected sheet gives following error when user tries type in data in new worksheet.

error: "the cell or chart you're trying change on protected sheet"

click ok on error message.

please note error happens once. click ok on pop error message , type again, excel allows type data in cells , save sheet.

we have excel (format .xls) file being used create excel spreadsheet when button on form in same spreadsheet clicked. copies 1 password protected blank sheet (a template) new workbook unprotected sheet. code below used work excel 2007(using .xls format). upgraded excel 2007 excel 2013 , problem appeared.

private sub cmd_click() dim jbook workbook dim jsheet worksheet  currentworkbook = activeworkbook.name workbooks(currentworkbook).unprotect jworksheetpassword 'catch errors on error goto errend    dim orginalscreenupdating boolean orginalscreenupdating = application.screenupdating application.screenupdating = false  if range("language").value = "2"    'french   set jsheet = templatefr else    'english    set jsheet = templateen end if  jsheet.visible = xlsheethidden 'jsheet.visible = xlsheetvisible  'delete line jsheet.unprotect jworksheetpassword  set jbook = workbooks.add(xlwbatworksheet) jsheet.copy after:=jbook.sheets(1) jbook.sheets(2).visible = xlsheetvisible  application.displayalerts = false jbook.sheets(1).delete application.displayalerts = true  jsheet.visible = xlsheetveryhidden  'delete line jbook.sheets(1).unprotect jworksheetpassword 'delete line 'jsheet.protect password:=jworksheetpassword  noerrend: workbooks(currentworkbook).protect password:=jworksheetpassword, structure:=true, windows:=false application.screenupdating = orginalscreenupdating unload me exit sub  errend: workbooks(currentworkbook).protect password:=jworksheetpassword, structure:=true, windows:=false application.displayalerts = true application.screenupdating = true msgbox datatable.range("msg4").value, vbcritical,    datatable.range("msg4title").value unload me  end sub

the following lines of code activate original workbook , somehow clears protection of copied sheet excel 2013 only. on excel 2007 causes original workbook activated , confuses users, hence check 2013.

if application.version = "15.0"     workbooks(currentworkbook).activate     'jbook.activate end if

this hack happens work. if 1 finds better solution please post here well.

the full code listing follows:

private sub cmd_click() dim jbook workbook dim jsheet worksheet  currentworkbook = activeworkbook.name workbooks(currentworkbook).unprotect jworksheetpassword 'catch errors on error goto errend dim orginalscreenupdating boolean orginalscreenupdating = application.screenupdating application.screenupdating = false  if range("language").value = "2"     'french     set jsheet = templatefr else     'english     set jsheet = templateen end if  jsheet.visible = xlsheethidden  set jbook = workbooks.add(xlwbatworksheet) jsheet.copy after:=jbook.sheets(1) jbook.sheets(2).visible = xlsheetvisible  application.displayalerts = false jbook.sheets(1).delete application.displayalerts = true  if application.version = "15.0"     workbooks(currentworkbook).activate     'jbook.activate end if  jsheet.visible = xlsheetveryhidden  noerrend: workbooks(currentworkbook).protect password:=jworksheetpassword, structure:=true, windows:=false application.screenupdating = orginalscreenupdating unload me exit sub  errend: workbooks(currentworkbook).protect password:=jworksheetpassword,     structure:=true, windows:=false application.displayalerts = true application.screenupdating = true msgbox datatable.range("msg4").value, vbcritical,     datatable.range("msg4title").value unload me   end sub

Comments

Post a Comment

Popular posts from this blog

authentication - Mongodb revoke acccess to connect test database -

i have ec2 instance mongodb 3.2.0 installed. have user admin access. can connect user , perform operation remotely. 1 can connect database test remote if knows ip mongo 11.11.11.11. although not able make operations, want restrict access user credentials can connect server. first need create users , grant roles users, example: use admin db.createuser( { user: "myuseradmin", pwd: "abc123", roles: [ { role: "useradminanydatabase", db: "admin" } ] } ) then need start server --auth parameter mongod --auth more information here
Read more

r - Update two sets of radiobuttons reactively - shiny -

i have read ( how make choices in radiobuttons reactive in shiny? ) shows me how update radiobutton s in reactive way. however, when try , update 2 sets of buttons same data, 1 set renders. example: server: # create example data wafer <- rep(c(1:3), each=3) length <- c(1,1,2,1,1,1,3,5,1) width <- c(3,1,6,1,1,1,1,1,6) dd <- data.frame(wafer, length, width) shinyserver(function(input, output, session){ # create reactive dataframe store data values <- reactivevalues() values$df <- data.frame() # lengths , widths of wafer user input <- eventreactive(input$do, { subset(dd, wafer %in% input$wafer, select = length:width) }) # update reactive data frame lengths , widths have been selected user input observe({ if(!is.null(a())) { values$df <- rbind(isolate(values$df), a()) } }) output$wl <- renderdatatable({ a() }) # update radio buttons unique length , widths stored in values$df # ever "observe" put first in co...
Read more

ios - Realm over CoreData should I use NSFetchedResultController or a Dictionary? -

i'm working on app using core data , nsfetchedresultcontroller many reasons switch use realm. saw there "realm version" of nsfetchedresultcontroller on github wouldn't compatible current code using core data. the view controller displaying list of people same school, address book. list sublist of people studied in same city. so thinking make 1 unique request database retrieve list of people , filter locally list within 1 dictionary per school [string: anyobject] string section name within tableview , anyobject array of people. first solution, make nsfetchedresultcontroller each school predicate filtering location. delete actions etc.. handled delegates -> not compatible realm create dictionaries , update them each actions... -> works realm it's annoying code. any better solution? edit: i need clarify request: i'd write class inherit uitableviewcontroller. this class has list of people sorted in alphabetical order the tableview has ...
Read more