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

In reply to Re^3: Win32::Ole excel external data range by davies
in thread Win32::Ole excel external data range by anti-monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.