Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie Confused...
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 -- ex130 ----------------------------------------------------------------------- ex1302's Profile: http://www.excelforum.com/member.php...fo&userid=2521 View this thread: http://www.excelforum.com/showthread.php?threadid=38707 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie Confused...
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie Confused...
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie Confused...
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 : -- ex130 ----------------------------------------------------------------------- ex1302's Profile: http://www.excelforum.com/member.php...fo&userid=2521 View this thread: http://www.excelforum.com/showthread.php?threadid=38707 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie Confused...
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie Confused...
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 : -- ex130 ----------------------------------------------------------------------- ex1302's Profile: http://www.excelforum.com/member.php...fo&userid=2521 View this thread: http://www.excelforum.com/showthread.php?threadid=38707 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie Confused...
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie Confused...
Bob, I realised it should be on App__SheetChange thanks for the help, Rgds, Andy -- ex1302 ------------------------------------------------------------------------ ex1302's Profile: http://www.excelforum.com/member.php...o&userid=25217 View this thread: http://www.excelforum.com/showthread...hreadid=387079 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie Confused...
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" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie Confused...
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie Confused...
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
confused in the o.c. | New Users to Excel | |||
confused. Please help. | Excel Discussion (Misc queries) | |||
Real Newbie newbie question | New Users to Excel | |||
confused... | Excel Programming | |||
Confused | Excel Programming |