Here is part 2! In this code, I demonstrate the two types of drop down lists, adding a combobox to one of the sheets on the fly. I haven't created a form on the fly, as I imagine any form you would want would be static. You don't need to link the combobox to a cell, but it can be useful so I have demonstrated it. A1 won't reveal itself to be a dropdown unless it is the active cell. But both pull their list from the dynamic range on the other sheet. Positioning the combobox can be done using the same techniques I developed in response to 819178.

use strict;
use warnings;
use Win32::OLE;

my $xl = Win32::OLE->new('Excel.Application');
$xl->{EnableEvents}   = 0;
$xl->{Visible} = 1;

my $wb = $xl->Workbooks->Add;

#Set up the sheets
my $nSheets = $wb->Sheets->Count;
if ($nSheets == 1) {
    $wb->Sheets->Add({After=>$wb->Sheets(1)});
}
if ($nSheets > 2) {
    for (3 .. $nSheets) {
        $wb->Sheets(3)->Delete;
    }
}
my $shtDrop = $wb->Sheets(1);
my $shtList = $wb->Sheets(2);
$shtDrop->{Name} = "DropBoxes";
$shtList->{Name} = "List";

#Create named ranges
$wb->Names->Add({Name=>'zTopList', RefersTo=>'=List!$A$1'});
$wb->Names->Add({Name=>'zEndList', RefersTo=>'=List!$A$2'});
$wb->Names->Add({Name=>'zList', RefersTo=>'=OFFSET(zTopList,1,0,ROW(zE
+ndList)-ROW(zTopList)-1,COLUMN(zEndList)-COLUMN(zTopList)+1)'});

#Create a list of valid options for the dropdown
$shtList->Range('zTopList')->{Value}='A';
$shtList->Range('zEndList')->{Value} = "-";
$shtList->Range('zEndList')->{HorizontalAlignment} = 5; #xlFill
for (1 .. 9) { #Insert some data
    $shtList->Range('zEndList')->EntireRow->Insert;
    $shtList->Cells($_ + 1, 1)->{Value} = $_;
}

#Data Validation DropDown
$shtDrop->Range('A1')->Validation->Add({Type      => 3, #xlValidateLis
+t
                                        AlertStyle=> 1, #xlValidAlertS
+top
                                        Operator  => 1, #xlBetween
                                        Formula1  =>'=zList'});
$shtDrop->Range('A1')->Validation->{IgnoreBlank}    = 0;
$shtDrop->Range('A1')->Validation->{InCellDropdown} = 1;
$shtDrop->Range('A1')->Validation->{ShowInput}      = 1;
$shtDrop->Range('A1')->Validation->{ShowError}      = 1;

#ComboBox
my $cbo = $shtDrop->OLEObjects->Add({ClassType    => "Forms.ComboBox.1
+",
                                     DisplayAsIcon=> 0,
                                     Left         => 48,
                                     Top          => 26.25,
                                     Width        => 96.75,
                                     Height       => 25.5});
$cbo->{LinkedCell}    = 'A2';
$cbo->{ListFillRange} = 'zList';

$shtDrop->Activate;
$xl->{EnableEvents}   = 1;
[download]

Regards,

John Davies