excel - VBA prevent user changing cell value with reference to only the initial cell value -


i'm trying stop fields being changed user. don't know columns fields in, value contain.

my current approach this:

private sub workbook_sheetchange(byval sh object, byval target range)         dim columnheaderrange range set shtdata = worksheets("data")      set columnheaderrange = union(shtdata.columns(columnnumber(5, "example1")), _                          shtdata.columns(columnnumber(5, "example2")), _                          shtdata.columns(columnnumber(5, "example3")))     set columnheaderrange = application.intersect(target, columnheaderrange)         elseif not (columnheaderrange nothing)         application         .enableevents = false         .undo         msgbox "change not possible.", 16         .enableevents = true     end           else     exit sub end if

my columnnumber function in above code takes row , field value parameters , returns column number. since i'm using fixed field values though, fails if field has been changed union call fails.

is there way have code run upon user attempting change value of cell before actual value of cell changed? alternatively can suggest better approach?

further comments

example 1

create sheet called list, store values. best part method not have amend code every time want add/delete items list.

see screenshot

enter image description here

and let's main sheet

enter image description here

paste code in sheet code area

dim rnglist range, acell range dim rowar() long  private sub worksheet_change(byval target range)     dim long      on error goto whoa      application.enableevents = false      each acell in target         if acell.row = 5             application                 = lbound(rowar) ubound(rowar)                     if rowar(i) = acell.column                         msgbox "change not possible."                         .undo                         goto letscontinue                     end if                 next             end         end if     next  letscontinue:     application.enableevents = true     exit sub whoa:     msgbox err.description     resume letscontinue end sub  private sub worksheet_selectionchange(byval target range)     dim wslist worksheet     dim n long, lrow long      set wslist = thisworkbook.sheets("list")      wslist         lrow = .range("a" & .rows.count).end(xlup).row         set rnglist = .range("a1:a" & lrow)     end      n = 0     redim rowar(n)      each acell in range("5:5")         if len(trim(acell.value)) <> 0             if application.worksheetfunction.countif(rnglist, acell.value) > 0                 n = n + 1                 redim preserve rowar(n)                 rowar(n) = acell.column                 debug.print acell.column             end if         end if     next end sub

enter image description here

code in action

enter image description here

example 2

this uses hardcoded list.

option explicit  dim rowar() long, acell range  private sub worksheet_selectionchange(byval target range)     dim mylist string, myar() string     dim n long, long      '~~> list     mylist = "header 1,header 2"     myar = split(mylist, ",")      n = 0     redim rowar(n)      each acell in range("5:5")         if len(trim(acell.value)) <> 0             = lbound(myar) ubound(myar)                 if acell.value = myar(i)                     n = n + 1                     redim preserve rowar(n)                     rowar(n) = acell.column                 end if             next         end if     next end sub  private sub worksheet_change(byval target range)     dim long      on error goto whoa      application.enableevents = false      each acell in target         if acell.row = 5             application                 = lbound(rowar) ubound(rowar)                     if rowar(i) = acell.column                         msgbox "change not possible."                         .undo                         goto letscontinue                     end if                 next             end         end if     next  letscontinue:     application.enableevents = true     exit sub whoa:     msgbox err.description     resume letscontinue end sub

Comments

Post a Comment

Popular posts from this blog

Why does Ruby on Rails generate add a blank line to the end of a file? -

running rails generate controller foo home results in: class foocontroller < application controller def home end end (there's nothing on line in actual file, blank line) is there purpose blank line? it's common use newline indicate end of line, last line. many editors (like vi, use) add in newline silently. consensus text files (especially in unix world) should end in newline, , there have been problems historically if wasn't present. why should text files end newline? the tool used count lines in file "wc" counts newlines in file, without trailing newline show 3 instead of 4. it improves readability of templates used in generator. consider: https://github.com/rails/rails/blob/master/railties/lib/rails/generators/rails/controller/templates/controller.rb to remove trailing newline, template have last line of: end<% end -%> instead of: end <% end -%> that seems less readable me. the discussion below refers ...
Read more

keyboard - Smiles and long press feature in Android -

i've created android keyboard. how add features inserting smilies, , using long keypresses display symbols? to create long-press behavior allows select several options pop-up menu, follow guide (specifically pop-up menu section): http://developer.android.com/guide/topics/ui/menus.html smileys (also known emoticons) combination of keys, understood programs , parsed same way. have do, when clicks smiley face on keyboard, simulate pressing of key combination matching emoticon. example, when clicks smiley, should simulate key presses ':' , ')' :). here's list of emoticons: http://en.wikipedia.org/wiki/list_of_emoticons
Read more

node.js - Bad Request - node js ajax post -

i'm using express 3.0 , node v0.11.0. have button submits form , i'm using ajax post data json object node server. client code is: $('#contact').submit(function(e) { console.log('submit called'); var formdata = json.stringify($('form').serializeobject()); console.log(formdata); $.ajax({ url: "http://localhost:3000/savedata/", type: "post", datatype: 'json', data: {objectdata: formdata}, contenttype: "application/json", complete: function() { console.log('process complete'); }, success: function(data) { console.log('process sucess'); }, error: function() { console.log('process error'); }, }); return false; }); then on server: var express = require('express'); var app = express(); app.set('views', __dirname + '/views'); app.set('view engine', 'jade'); app.use(express.bodyparser());...
Read more