Sorry typo
Set oNewWb = Workbooks.Add
With oNewWb.Worksheets("Sheet1").Range("H10").Validatio n
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:= xlBetween, _
Formula1:="=$M$1:$M$10"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
--
HTH
Bob Phillips
"Bob Phillips" wrote in message
...
Andy,
we may take a few steps here, but for the first one.
Set oNewWb = Workbooks.Add
With oNewWb.Worksheets("Sheet1").Range("H10").Validatio n
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:=
_
xlBetween, Formula1:="M1:M10"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Creates a DV in H10.
You still need the forms and the Worksheet change code. We could do the
latter with application events, post the current code and we can convert.
I
assume the forms can be in the add-in? where will the DV list be?
--
HTH
Bob Phillips
"ex1302" wrote in
message ...
Hi all,
I have been vba'ing in ms access for a few years and due to job change
I am now primarily working with excel, so i am therefore a real excel
newbie! :)
The problem:
I have built a happy little process whereby if a user selects a certain
value from a data validation list embedded in the sheet, a seperate
userform will open for each value. This relies on the specific sheet
"Worksheet_Change" to drive the functions.
This works fine as it is all in the same place, however the powers
that be have decided that this needs to be added to a seperate new
workbook each time an admin downloads a specific report, and this is
where I am stuck!
How can I get this code to be portable to other workbooks?
I have exported the main code (inc all the forms) as a excel add-in but
i have to physically run a macro to initialise the open form code after
each change in each cell!
I was hoping the add-in would include the chance of adding the data
valdation or providing an additional button, and it is this part i am
confused about:
If the work book is fresh how will I get the data validtion in without
the user having to doing too much?
How can I then get the data validation to trigger the open form code?
Or get the openform code behind the new sheet?
Sorry for the essay,
Thanks
Andy
--
ex1302
------------------------------------------------------------------------
ex1302's Profile:
http://www.excelforum.com/member.php...o&userid=25217
View this thread:
http://www.excelforum.com/showthread...hreadid=387079