adventagious
04-26-2011, 12:20 PM
I am using VBA enhanced Excel workbooks for training event registration internally for our company. These are protected workbooks with only certain fields available for editing, enabling a few dozen training managers to enroll their people into classes. There is a predetermined number of seats in the class.
I am currently restructuring how the workbooks function. Per the attached picture, Column K is using a data validation list with two options. The options are Available and Reserved. I want a training manager to enter their employee's data in columns L through O and then change the status from Available to Reserved.
When that change is made, I want the current row of columns K thru O to be selected and locked, protecting the "reservation" from being edited by anyone else except our training staff when the sheet is unprotected. I have the UserInterFaceOnly:=True option applied to the sheets in the workbook to allow macros to run while the sheet is protected.
The pertinent cells in column K (K5:K484) have been made a range with the name LockStatusRange. The pertinent cells in columns K thru O (K5:O484) have been made into a range with the name TMinputRange.
Based on what I've researched, my thought is to use SelectionChange on the activecell in LockStatusRange (K) to fire the vba to select and lock the current row of TMinputRange (K thru M). Am I on the right track here? or should this be approached differently?
I've implemented a number of Excel VBA modifications over the past few years, but it has been a little while, as I've been working with Access and SharePoint more lately. I'm having a hard time wrapping my mind around how to build this.
I'd love some guidance on how to code this correctly. Thanks a lot.
I am currently restructuring how the workbooks function. Per the attached picture, Column K is using a data validation list with two options. The options are Available and Reserved. I want a training manager to enter their employee's data in columns L through O and then change the status from Available to Reserved.
When that change is made, I want the current row of columns K thru O to be selected and locked, protecting the "reservation" from being edited by anyone else except our training staff when the sheet is unprotected. I have the UserInterFaceOnly:=True option applied to the sheets in the workbook to allow macros to run while the sheet is protected.
The pertinent cells in column K (K5:K484) have been made a range with the name LockStatusRange. The pertinent cells in columns K thru O (K5:O484) have been made into a range with the name TMinputRange.
Based on what I've researched, my thought is to use SelectionChange on the activecell in LockStatusRange (K) to fire the vba to select and lock the current row of TMinputRange (K thru M). Am I on the right track here? or should this be approached differently?
I've implemented a number of Excel VBA modifications over the past few years, but it has been a little while, as I've been working with Access and SharePoint more lately. I'm having a hard time wrapping my mind around how to build this.
I'd love some guidance on how to code this correctly. Thanks a lot.