| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issue with workbook not being selected when form button is clicked
Hi all,
I have an issue where I have a form come up that requires the user to select a checkbox and then select a "Proceed" button that results in code being executed and then the form closing. The issue I have is that when the form is opened, if I then click into another spreadsheet that I have open and then click on say the "Proceed" button of the form (which applies to the other spreadsheet), I get an error because the code is applied to the other spreadsheet (not the one that the form is intended for). I'm wondering what the best way is to get around this issue. I would just hardcode in the name of the file for which the code is intended for only that there are many different people that will use the spreadsheet and they can save a different name to the file. What I tried to do was put the the following formulas into the spreadsheet in order to get the name of the spreadsheet and then use the code below to select the spreadsheet -- the issue is that for some reason, the spreadsheet formulas sometimes show "SOLVER" as the file name which is incorrect..then when I double click in the cell, the correct name of the file shows again. Spreadsheet formulas: - cell BA1 formula: =CELL("filename") - cell BB1 formula: =IF(ISERROR(LEFT(BA1,SEARCH("]",BA1)-5)),LEFT(BA1,SEARCH(".",BA1)-1),LEFT(BA1,SEARCH("]",BA1)-5)) - cell BC1 formula: =IF(ISERROR(RIGHT(BB1,LEN(BB1)-SEARCH("[",BB1))),BB1,RIGHT(BB1,LEN(BB1)-SEARCH("[",BB1))) Code executed when "Proceed" button of form clicked ("strworkbname" is the value within cell BC1): Windows(strworkbname).Activate -- Robert |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Issue with workbook not being selected when form button is clicked
if you want the code to run on the same workbook in which the form is
located, try using ThisWorkBook.Activate "robs3131" wrote: Hi all, I have an issue where I have a form come up that requires the user to select a checkbox and then select a "Proceed" button that results in code being executed and then the form closing. The issue I have is that when the form is opened, if I then click into another spreadsheet that I have open and then click on say the "Proceed" button of the form (which applies to the other spreadsheet), I get an error because the code is applied to the other spreadsheet (not the one that the form is intended for). I'm wondering what the best way is to get around this issue. I would just hardcode in the name of the file for which the code is intended for only that there are many different people that will use the spreadsheet and they can save a different name to the file. What I tried to do was put the the following formulas into the spreadsheet in order to get the name of the spreadsheet and then use the code below to select the spreadsheet -- the issue is that for some reason, the spreadsheet formulas sometimes show "SOLVER" as the file name which is incorrect..then when I double click in the cell, the correct name of the file shows again. Spreadsheet formulas: - cell BA1 formula: =CELL("filename") - cell BB1 formula: =IF(ISERROR(LEFT(BA1,SEARCH("]",BA1)-5)),LEFT(BA1,SEARCH(".",BA1)-1),LEFT(BA1,SEARCH("]",BA1)-5)) - cell BC1 formula: =IF(ISERROR(RIGHT(BB1,LEN(BB1)-SEARCH("[",BB1))),BB1,RIGHT(BB1,LEN(BB1)-SEARCH("[",BB1))) Code executed when "Proceed" button of form clicked ("strworkbname" is the value within cell BC1): Windows(strworkbname).Activate -- Robert |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Issue with workbook not being selected when form button is cli
That works!! Thank you so much!
-- Robert "JMB" wrote: if you want the code to run on the same workbook in which the form is located, try using ThisWorkBook.Activate "robs3131" wrote: Hi all, I have an issue where I have a form come up that requires the user to select a checkbox and then select a "Proceed" button that results in code being executed and then the form closing. The issue I have is that when the form is opened, if I then click into another spreadsheet that I have open and then click on say the "Proceed" button of the form (which applies to the other spreadsheet), I get an error because the code is applied to the other spreadsheet (not the one that the form is intended for). I'm wondering what the best way is to get around this issue. I would just hardcode in the name of the file for which the code is intended for only that there are many different people that will use the spreadsheet and they can save a different name to the file. What I tried to do was put the the following formulas into the spreadsheet in order to get the name of the spreadsheet and then use the code below to select the spreadsheet -- the issue is that for some reason, the spreadsheet formulas sometimes show "SOLVER" as the file name which is incorrect..then when I double click in the cell, the correct name of the file shows again. Spreadsheet formulas: - cell BA1 formula: =CELL("filename") - cell BB1 formula: =IF(ISERROR(LEFT(BA1,SEARCH("]",BA1)-5)),LEFT(BA1,SEARCH(".",BA1)-1),LEFT(BA1,SEARCH("]",BA1)-5)) - cell BC1 formula: =IF(ISERROR(RIGHT(BB1,LEN(BB1)-SEARCH("[",BB1))),BB1,RIGHT(BB1,LEN(BB1)-SEARCH("[",BB1))) Code executed when "Proceed" button of form clicked ("strworkbname" is the value within cell BC1): Windows(strworkbname).Activate -- Robert |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Issue with workbook not being selected when form button is cli
quite welcome, thanks for posting back
"robs3131" wrote: That works!! Thank you so much! -- Robert "JMB" wrote: if you want the code to run on the same workbook in which the form is located, try using ThisWorkBook.Activate "robs3131" wrote: Hi all, I have an issue where I have a form come up that requires the user to select a checkbox and then select a "Proceed" button that results in code being executed and then the form closing. The issue I have is that when the form is opened, if I then click into another spreadsheet that I have open and then click on say the "Proceed" button of the form (which applies to the other spreadsheet), I get an error because the code is applied to the other spreadsheet (not the one that the form is intended for). I'm wondering what the best way is to get around this issue. I would just hardcode in the name of the file for which the code is intended for only that there are many different people that will use the spreadsheet and they can save a different name to the file. What I tried to do was put the the following formulas into the spreadsheet in order to get the name of the spreadsheet and then use the code below to select the spreadsheet -- the issue is that for some reason, the spreadsheet formulas sometimes show "SOLVER" as the file name which is incorrect..then when I double click in the cell, the correct name of the file shows again. Spreadsheet formulas: - cell BA1 formula: =CELL("filename") - cell BB1 formula: =IF(ISERROR(LEFT(BA1,SEARCH("]",BA1)-5)),LEFT(BA1,SEARCH(".",BA1)-1),LEFT(BA1,SEARCH("]",BA1)-5)) - cell BC1 formula: =IF(ISERROR(RIGHT(BB1,LEN(BB1)-SEARCH("[",BB1))),BB1,RIGHT(BB1,LEN(BB1)-SEARCH("[",BB1))) Code executed when "Proceed" button of form clicked ("strworkbname" is the value within cell BC1): Windows(strworkbname).Activate -- Robert |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| New record button grayed out on auto form w/ shared workbook | Excel Discussion (Misc queries) | |||
| let a sub recognise the name of the button that has been clicked. | Excel Discussion (Misc queries) | |||
| Charts appear snowy (e.g., tv) unless clicked on. Memory issue? | Charts and Charting in Excel | |||
| Do until button clicked | Excel Programming | |||
| Pause macro, add form button to sheet, continue macro when button clicked! | Excel Programming | |||
Linear Mode
