Newbie Confused...

[ex1302]

Hi all,

I have been vba'ing in ms access for a few years and due to job chang
I am now primarily working with excel, so i am therefore a real exce
newbie!

The problem:

I have built a happy little process whereby if a user selects a certai
value from a data validation list embedded in the sheet, a seperat
userform will open for each value. This relies on the specific shee
"Worksheet_Change" to drive the functions.

This works fine as it is all in the same place, however the power
that be have decided that this needs to be added to a seperate ne
workbook each time an admin downloads a specific report, and this i
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 bu
i have to physically run a macro to initialise the open form code afte
each change in each cell!

I was hoping the add-in would include the chance of adding the dat
valdation or providing an additional button, and it is this part i a
confused about:

If the work book is fresh how will I get the data validtion in withou
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

And

 

[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?

 

[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

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?

 

[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 ca
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 hav
exported this as an addin...

lets say the user add's the "add-in" in to their brand new sheet, a
the moment nothing happens.... which i can understand... but how can
get the DV list to initialise and somehow embed the code on to the shee
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 th
worksheet_change event, how could i get this to work from an add-i
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

 

[ex1302]

Thanks Bob,

I'll give it a go as soon as I can, i hadnt realised that you coul
write application events, hmmm i will have to spend more time surfin
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.

 

[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