VBA to lock cells containing data

[dave_br]

Hi Guys,

rdw7277 helped me out the other day with a code but i am still having issues with this.
i am looking to lock areas of the sheet once a reference number is inserted. it seems like the beforesave function is best in VBA. i have included a copy of a couple of lines of the sheet. the whole sheet is locked apart from where reference numbers can be inserted.

<TABLE style="WIDTH: 507pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=681><COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" span=17 width=35><TBODY><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 65pt; HEIGHT: 14.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=86> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>13</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>16</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl64 width=35>17</TD></TR><TR style="HEIGHT: 30.75pt; mso-height-source: userset" height=41><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 30.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=41>Monday</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl67> </TD></TR><TR style="HEIGHT: 30.75pt; mso-height-source: userset" height=41><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 30.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=41>Tuesday</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl67> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext; BORDER-RIGHT: #ece9d8" class=xl67> </TD></TR></TBODY></TABLE>

any help is appreciated.

thanks
dave

 

[Joe4]

Please continue on in the original thread: http://www.mrexcel.com/forum/showthread.php?t=579375

All clarifications, follow-ups (continuations), and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: http://www.mrexcel.com/forum/showthread.php?t=99490).