Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
pop up window
Is it possible to have a small pop up window to appear each time after you
putting the data and pop up window asks for which worksheet to open? I would be extremely grateful if someone can guide me. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
pop up window
The following worksheet event code monitors cell B9. Once you have entered a
value in B9, a window pops up to allow you to select a file and open it: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set B9 = Range("B9") If Intersect(t, B9) Is Nothing Then Exit Sub s = Application.GetOpenFilename Workbooks.Open Filename:=s End Sub Because it is worksheet code, it is very easy to install and automatic to use: 1. right-click the tab name near the bottom of the Excel window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you have any concerns, first try it on a trial worksheet. If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200808 "tleehh" wrote: Is it possible to have a small pop up window to appear each time after you putting the data and pop up window asks for which worksheet to open? I would be extremely grateful if someone can guide me. Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
pop up window
Thank you for your answer. It works fine. But this is what i am trying to do.
I have a file that contains 26 worksheets. I am trying to have a pop up window each time a value is entered. After a value is entered, it asks which worksheet to pick next. Thanks. Gary''s Student" wrote: The following worksheet event code monitors cell B9. Once you have entered a value in B9, a window pops up to allow you to select a file and open it: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set B9 = Range("B9") If Intersect(t, B9) Is Nothing Then Exit Sub s = Application.GetOpenFilename Workbooks.Open Filename:=s End Sub Because it is worksheet code, it is very easy to install and automatic to use: 1. right-click the tab name near the bottom of the Excel window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you have any concerns, first try it on a trial worksheet. If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200808 "tleehh" wrote: Is it possible to have a small pop up window to appear each time after you putting the data and pop up window asks for which worksheet to open? I would be extremely grateful if someone can guide me. Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
pop up window
Hi,
You can modify the code you were given: Private Sub Worksheet_Change(ByVal Target As Range) Dim insect As Range Dim Y As String Set insect = Intersect(Target, Range("B1:B10")) If insect Is Nothing Then Exit Sub Y = InputBox("What sheet do you want to put this in?") Sheets(Y).Activate End Sub You realize, of course, that you are not doing anything but moving to the sheet. I suspect you want to do more but you haven't told us what. If this helps, please click the Yes button. -- Thanks, Shane Devenshire "tleehh" wrote: Thank you for your answer. It works fine. But this is what i am trying to do. I have a file that contains 26 worksheets. I am trying to have a pop up window each time a value is entered. After a value is entered, it asks which worksheet to pick next. Thanks. Gary''s Student" wrote: The following worksheet event code monitors cell B9. Once you have entered a value in B9, a window pops up to allow you to select a file and open it: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set B9 = Range("B9") If Intersect(t, B9) Is Nothing Then Exit Sub s = Application.GetOpenFilename Workbooks.Open Filename:=s End Sub Because it is worksheet code, it is very easy to install and automatic to use: 1. right-click the tab name near the bottom of the Excel window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you have any concerns, first try it on a trial worksheet. If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200808 "tleehh" wrote: Is it possible to have a small pop up window to appear each time after you putting the data and pop up window asks for which worksheet to open? I would be extremely grateful if someone can guide me. Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
pop up window
Thank you very Much! this is exactly what i was trying to accomplish. Thank
you... What if you have larger cell range to work with. lets say: B1:B10 C1:C10 D1:D10 E1:E10 F1:F10 G1:G10 Thank you. "ShaneDevenshire" wrote: Hi, You can modify the code you were given: Private Sub Worksheet_Change(ByVal Target As Range) Dim insect As Range Dim Y As String Set insect = Intersect(Target, Range("B1:B10")) If insect Is Nothing Then Exit Sub Y = InputBox("What sheet do you want to put this in?") Sheets(Y).Activate End Sub You realize, of course, that you are not doing anything but moving to the sheet. I suspect you want to do more but you haven't told us what. If this helps, please click the Yes button. -- Thanks, Shane Devenshire "tleehh" wrote: Thank you for your answer. It works fine. But this is what i am trying to do. I have a file that contains 26 worksheets. I am trying to have a pop up window each time a value is entered. After a value is entered, it asks which worksheet to pick next. Thanks. Gary''s Student" wrote: The following worksheet event code monitors cell B9. Once you have entered a value in B9, a window pops up to allow you to select a file and open it: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set B9 = Range("B9") If Intersect(t, B9) Is Nothing Then Exit Sub s = Application.GetOpenFilename Workbooks.Open Filename:=s End Sub Because it is worksheet code, it is very easy to install and automatic to use: 1. right-click the tab name near the bottom of the Excel window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you have any concerns, first try it on a trial worksheet. If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200808 "tleehh" wrote: Is it possible to have a small pop up window to appear each time after you putting the data and pop up window asks for which worksheet to open? I would be extremely grateful if someone can guide me. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to save a desired window size but hv window comeup fullsz by d | Excel Discussion (Misc queries) | |||
View cell contents as a pop-up window (similar to comments window) | Excel Worksheet Functions | |||
Docking Project Explorer, Properties window and Code window in VBE | Setting up and Configuration of Excel | |||
The window opens in a smaller window not full sized window. | Excel Discussion (Misc queries) | |||
In Excel - how to delete new window created from window on tool ba | Excel Worksheet Functions |