Newbie Confused...

[ex1302]

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

[Bob Phillips]

Andy,

we may take a few steps here, but for the first one.

Set oNewWb = Workbooks.Add
With oNewWb.Worksheets("Sheet1").Range("H10").Validation
.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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

Sorry typo

Set oNewWb = Workbooks.Add
With oNewWb.Worksheets("Sheet1").Range("H10").Validation
.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" <[email protected]> wrote in message
news:[email protected]...
> Andy,
>
> we may take a few steps here, but for the first one.
>
> Set oNewWb = Workbooks.Add
> With oNewWb.Worksheets("Sheet1").Range("H10").Validation
> .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" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>

[ex1302]

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?

Thanks for the reply, I had given up hope!


I have the forms and the macro to add the necessary DV list, and have exported this as an addin...

lets say the user add's the "add-in" in to their brand new sheet, at the moment nothing happens.... which i can understand... but how can i get the DV list to initialise and somehow embed the code on to the sheet to call the forms, or am i going about that the wrong way?

i.e at the moment the code for the form call (show) is on the worksheet_change event, how could i get this to work from an add-in perspective?

Thanks again

[Bob Phillips]

I would suggest that you are. As I said, I would add application events to
your addin which would then apply to all worksheets, and remove the sheet
specific change event code.

I have knocked some code up which you put in the add-in Thisworkbook code
module, and it tests if cell H10 is a DV, if so it triggers, You can add you
form launch code in there.

Option Explicit

Public WithEvents App As Application

Private Const rngDV As String = "$H$10"

Private Sub Workbook_Open()
Set App = Application
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = rngDV Then
If Not Intersect(Target,
Cells.SpecialCells(xlCellTypeAllValidation)) Is Nothing Then
'do your stuff
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
HTH

Bob Phillips

"ex1302" <[email protected]> wrote in
message news:[email protected]...
>
> > 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?
> >
>
> Thanks for the reply, I had given up hope!
>
>
> I have the forms and the macro to add the necessary DV list, and have
> exported this as an addin...
>
> lets say the user add's the "add-in" in to their brand new sheet, at
> the moment nothing happens.... which i can understand... but how can i
> get the DV list to initialise and somehow embed the code on to the sheet
> to call the forms, or am i going about that the wrong way?
>
> i.e at the moment the code for the form call (show) is on the
> worksheet_change event, how could i get this to work from an add-in
> perspective?
>
> Thanks again
>
>
> --
> ex1302
> ------------------------------------------------------------------------
> ex1302's Profile:
http://www.excelforum.com/member.php...o&userid=25217
> View this thread: http://www.excelforum.com/showthread...hreadid=387079
>

[ex1302]

Thanks Bob,

I'll give it a go as soon as I can, i hadnt realised that you could write application events, hmmm i will have to spend more time surfing the net for more info.

Thanks again,

Andy

[ex1302]

Bob,

Thanks I have run the code and it seems to partailly work.....

i ammended it for a test and put a validation list in cell A1, however i cant seem to get the test message (msgbox "Things changed") on Workbook_SheetChange to run?

Any ideas?

Andy

Option Explicit
Public WithEvents App As Application
Private Const rngDV As String = "$A$1"

Private Sub Workbook_Open()
Set App = Application
MsgBox "WB OPEN"
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

msgbox "Things changed"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = rngDV Then
If Not Intersect(Target, Cells.SpecialCells(xlCellTypeAllValidation)) Is Nothing Then
'do your stuff
End If
End If

ws_exit:
MsgBox "error"
Application.EnableEvents = True
End Sub

[ex1302]

Bob,

I realised it should be on App__SheetChange

thanks for the help,

Rgds,

Andy

[Bob Phillips]

It won't work until you open the add-in next time, as the application events
are not initialised. You could run Workbook_Open manually as an alternative.
You are aware you are dong it on the SelectChange event aren't you.

--
HTH

Bob Phillips

"ex1302" <[email protected]> wrote in
message news:[email protected]...
>
> Bob,
>
> Thanks I have run the code and it seems to partailly work.....
>
> i ammended it for a test and put a validation list in cell A1, however
> i cant seem to get the test message (msgbox "Things changed") on
> Workbook_SheetChange to run?
>
> Any ideas?
>
> Andy
>
> Option Explicit
> Public WithEvents App As Application
> Private Const rngDV As String = "$A$1"
>
> Private Sub Workbook_Open()
> Set App = Application
> MsgBox "WB OPEN"
> End Sub
>
> Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
> Range)
>
> msgbox "Things changed"
>
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Target.Address = rngDV Then
> If Not Intersect(Target, Cells.SpecialCells(xlCellTypeAllValidation))
> Is Nothing Then
> 'do your stuff
> End If
> End If
>
> ws_exit:
> MsgBox "error"
> Application.EnableEvents = True
> End Sub
>
>
> --
> ex1302
> ------------------------------------------------------------------------
> ex1302's Profile:
http://www.excelforum.com/member.php...o&userid=25217
> View this thread: http://www.excelforum.com/showthread...hreadid=387079
>

[ex1302]

It won't work until you open the add-in next time, as the application events
are not initialised. You could run Workbook_Open manually as an alternative.
You are aware you are dong it on the SelectChange event aren't you.

Bob,

I tried opening and closing the Workbook but the original Workbook_SheetChange event still didnt work, it only seemed to work when i changed it to App__SheetChange. Is this correct?


"You are aware you are dong it on the SelectChange event aren't you"
What do you mean?

Regards,

Andy

[Bob Phillips]

Yes, that is correct. I missed that you hadn't used App first time, but when
I saw that you noticed I assumed you were sorted, so didn't respond anymore.

Regards

Bob

"ex1302" <[email protected]> wrote in
message news:[email protected]...
>
> > It won't work until you open the add-in next time, as the application
> > events
> > are not initialised. You could run Workbook_Open manually as an
> > alternative.
> > You are aware you are dong it on the SelectChange event aren't you.
> >
>
> Bob,
>
> I tried opening and closing the Workbook but the original
> Workbook_SheetChange event still didnt work, it only seemed to work
> when i changed it to App__SheetChange. Is this correct?
>
>
> "You are aware you are dong it on the SelectChange event aren't you"
> What do you mean?
>
> Regards,
>
> Andy
>
>
> --
> ex1302
> ------------------------------------------------------------------------
> ex1302's Profile:
http://www.excelforum.com/member.php...o&userid=25217
> View this thread: http://www.excelforum.com/showthread...hreadid=387079
>