Issue with workbook not being selected when form button is clicked

[robs3131]

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

 

[JMB]

if you want the code to run on the same workbook in which the form is
located, try using ThisWorkBook.Activate

 

[robs3131]

That works!! Thank you so much!
--
Robert

if you want the code to run on the same workbook in which the form is
located, try using ThisWorkBook.Activate

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

 

[JMB]

quite welcome, thanks for posting back

That works!! Thank you so much!
--
Robert

if you want the code to run on the same workbook in which the form is
located, try using ThisWorkBook.Activate

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