dependable dropdown with macros

dependable_dropdown_with_macros.rb

require 'write_xlsx'

#workbook = WriteXLSX.new('write_xlsx_example.xlsx')
workbook = WriteXLSX.new('write_xlsx_example.xlsm')
workbook.add_vba_project('./vbaProject.bin')

dropdownSheet = workbook.add_worksheet('DropDown Values')
dropdownSheet.add_table('A1:A2', {
  data: [
    ['HOMBRES']
  ],
  name: 'LINEA',
  columns: [
    { header: 'Línea_Desc' }
  ]
})
dropdownSheet.add_table('B1:B2', {
  data: [
    ['AMBOS']
  ],
  name: 'HOMBRES',
  columns: [
    { header: 'SubLínea_Desc' }
  ]
})
dropdownSheet.add_table('C1:C3', {
  data: [
    ['AMBO BASICO'],
    ['AMBO TEMPORADA']
  ],
  name: 'AMBOS',
  columns: [
    { header: 'Clase_Desc' }
  ]
})
dropdownSheet.add_table('D1:D2', {
  data: [
    ['AMBO BASICO']
  ],
  name: 'AMBOBASICO',
  columns: [
    { header: 'SubClase_Desc' }
  ]
})
dropdownSheet.add_table('E1:E5', {
  data: [
    ['AMBO TEMPORADA POLY'],
    ['AMBO TEMPORADA POLY/LANA'],
    ['AMBO TEMPORADA LANA'],
    ['AMBO MEDIDA']
  ],
  name: 'AMBOTEMPORADA',
  columns: [
    { header: 'SubClase_Desc' }
  ]
})

dropdownSheet.protect('password')

worksheet = workbook.add_worksheet('eSKU')
worksheet.set_vba_name('hoja2')

locked = workbook.add_format(locked: 1)
unlocked = workbook.add_format(locked: 0)

worksheet.protect('password')

worksheet.set_column(0, 0, 20, unlocked)# columna A
worksheet.set_column(1, 0, 20, unlocked)# columna B
worksheet.set_column(2, 0, 20, unlocked)# columna C
worksheet.set_column(3, 0, 20, unlocked)# columna D

#worksheet.write('E1', 'Cell B1', locked);
#worksheet.write('A1', 'Cell A1', unlocked);

worksheet.data_validation('A1:A100',
{
    :validate        => 'list',
    :value           => "'DropDown Values'!A$2:A$4",
    :input_message   => 'Choose the value from the dropdown',
    :error_message   => 'Please use the dropdown selector to choose the value'
})

worksheet.data_validation('B1:B100',
{
    :validate        => 'list',
    :value           => '=INDIRECT(SUBSTITUTE(A1, " ", ""))',
    #:input_title     => 'Input an integer:',
    :input_message   => 'Choose the value from the dropdown',
    :error_message   => 'Please use the dropdown selector to choose the value'
})

worksheet.data_validation('C1:C100',
{
    :validate        => 'list',
    :value           => '=INDIRECT(SUBSTITUTE(B1, " ", ""))',
    #:input_title     => 'Input an integer:',
    :input_message   => 'Choose the value from the dropdown',
    :error_message   => 'Please use the dropdown selector to choose the value'
})

worksheet.data_validation('D1:D100',
{
    :validate        => 'list',
    :value           => '=INDIRECT(SUBSTITUTE(C1, " ", ""))',
    #:input_title     => 'Input an integer:',
    :input_message   => 'Choose the value from the dropdown',
    :error_message   => 'Please use the dropdown selector to choose the value'
})

worksheet.insert_button('E2', { :macro => 'unlockCells', :caption => 'unlock' })
worksheet.insert_button('E4', { :macro => 'lockCells', :caption => 'lock' })

# dropdownSheet.hide
# worksheet.set_first_sheet
# worksheet.activate
# workbook.close

#worksheet.set_first_sheet
dropdownSheet.hide()
workbook.close