Sunday, September 18, 2016

Checkbox name referring to a cell [Anwsered]

Checkbox name referring to a cell

Hi,

I want to create a checkbox with a name referring to a cell value.How can I do this? I have a list of about 20 possible components available for contracts in sheet "Parameters" and I would like to create checkboxes from them to a sheet "Scope". The idea of the parameters sheet is to make the maintanance/ updating of the excel easier.I would like to do the checkboxes so that if the options for components change in the parameters sheet (renamed, deleted or a new option is added), also thecheckboxes would be updated automatically (can happen e.g.by pushing a button that activates a macro.The point is that you don't have to add the checkbox and name it yourself).

E.g.I have two categories including several options in the Parameters sheet:SERVICES include options like car wash, waxing and paint jobPRODUCTS include options like: soap, wax and paintNow, I want to make checkboxes to Scope sheet, so that car wash, waxing and paint job are grouped together in a list and soap, wax and paint are grouped.Then, a sales person could check all the options included in a contract. If I would add a product, e.g. 'super soap', into the range named "PRODUCTS", the macro would add the checkbox for that (group together with other product checkboxes).

I have googled the solution and tried, for example, the Me.Controls (e.g.proposed here

http://answers.microsoft.com/en-us/office/forum/office_2007-excel/checkbox-name-referencing-a-cell/5b120f4e-2c2e-4c4b-9ff0-1a046963c892) but I can't get it work (error "Invalid use of Me keyword").I also tried the idea of "ActiveSheet.Shapes(CheckBox1).Select" and I managed to select (ActiveX) checkboxes with this, but didn't manage to rename them with the same logic.

Do I need to use ActiveX checboxes or does it matter? Do I need to use the UserForms in visual basic? I'm not used to them..Now I just added the checkboxes manually in excel and tried to do a macro in visual basic modules that would change their names.

THANKS for your help!!

ps.I know the basics of vba but I'm not that good yet, so I might have a bit stupid questions, sorry :)