Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make values of spreadsheet show in a modeless forms text bo
I have a Form that i display in Modeless state to allow the user make
selections and changes to spreadsheets without closing the form. I would like to have the activecell value of the spreadsheet show up in a textbox on the form. i have TWO questions: 1) I am using the personal.xls file for all the forms and macros to make it available to all workbooks, will the Workbook_SheetSelectionChange of personal.xls fire for the activeworkbook AND 2) Is there a way to indiacte that the modeless form is loaded and showing to captuer the event only if it is showing at the time? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make values of spreadsheet show in a modeless forms text bo
#1. Nope.
#1 and #2. You could have an application event that looks for a selection change. I used a different file than personal.xls... I put this in a general module: Option Explicit Public FormIsRunning As Boolean Sub startit() UserForm1.Show vbModeless End Sub I put this behind the ThisWorkbook module: Option Explicit Public WithEvents xlApp As Excel.Application Private Sub Workbook_Open() Set xlApp = Application End Sub Private Sub Workbook_Close() Set xlApp = Nothing End Sub Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) If FormIsRunning Then UserForm1.Label1.Caption = Target.Address(external:=True) End If End Sub I put this behind the userform: Option Explicit Private Sub UserForm_Initialize() FormIsRunning = True End Sub Private Sub UserForm_Terminate() FormIsRunning = False End Sub ======== If you're going to share this with others, don't use Personal.xls. You may end up messing up the other user's stuff. (And you share it with a workbook that's named nicely, too.) Chirs wrote: I have a Form that i display in Modeless state to allow the user make selections and changes to spreadsheets without closing the form. I would like to have the activecell value of the spreadsheet show up in a textbox on the form. i have TWO questions: 1) I am using the personal.xls file for all the forms and macros to make it available to all workbooks, will the Workbook_SheetSelectionChange of personal.xls fire for the activeworkbook AND 2) Is there a way to indiacte that the modeless form is loaded and showing to captuer the event only if it is showing at the time? -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make values of spreadsheet show in a modeless forms tex
Thank you Dave, but unfortunately all the functionalities of the App im
creating need to be available to ALL and ANY workbook since the activeworkbook can be any workbook, so i can't write workbook specific code. Will the fundamentals of this code still function correctly if i adapt it to work in personal.xls? "Dave Peterson" wrote: #1. Nope. #1 and #2. You could have an application event that looks for a selection change. I used a different file than personal.xls... I put this in a general module: Option Explicit Public FormIsRunning As Boolean Sub startit() UserForm1.Show vbModeless End Sub I put this behind the ThisWorkbook module: Option Explicit Public WithEvents xlApp As Excel.Application Private Sub Workbook_Open() Set xlApp = Application End Sub Private Sub Workbook_Close() Set xlApp = Nothing End Sub Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) If FormIsRunning Then UserForm1.Label1.Caption = Target.Address(external:=True) End If End Sub I put this behind the userform: Option Explicit Private Sub UserForm_Initialize() FormIsRunning = True End Sub Private Sub UserForm_Terminate() FormIsRunning = False End Sub ======== If you're going to share this with others, don't use Personal.xls. You may end up messing up the other user's stuff. (And you share it with a workbook that's named nicely, too.) Chirs wrote: I have a Form that i display in Modeless state to allow the user make selections and changes to spreadsheets without closing the form. I would like to have the activecell value of the spreadsheet show up in a textbox on the form. i have TWO questions: 1) I am using the personal.xls file for all the forms and macros to make it available to all workbooks, will the Workbook_SheetSelectionChange of personal.xls fire for the activeworkbook AND 2) Is there a way to indiacte that the modeless form is loaded and showing to captuer the event only if it is showing at the time? -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make values of spreadsheet show in a modeless forms tex
Why not put the userform and code in its own workbook. Then save it as an
addin. The user can put that file in the XLStart folder or load it via tools|Addins. And you don't have to worry about destroying someone else's work. == I don't see a reason why it wouldn't work in personal.xls, though. (I surely wouldn't use it, though. But I've said that.) Chirs wrote: Thank you Dave, but unfortunately all the functionalities of the App im creating need to be available to ALL and ANY workbook since the activeworkbook can be any workbook, so i can't write workbook specific code. Will the fundamentals of this code still function correctly if i adapt it to work in personal.xls? "Dave Peterson" wrote: #1. Nope. #1 and #2. You could have an application event that looks for a selection change. I used a different file than personal.xls... I put this in a general module: Option Explicit Public FormIsRunning As Boolean Sub startit() UserForm1.Show vbModeless End Sub I put this behind the ThisWorkbook module: Option Explicit Public WithEvents xlApp As Excel.Application Private Sub Workbook_Open() Set xlApp = Application End Sub Private Sub Workbook_Close() Set xlApp = Nothing End Sub Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) If FormIsRunning Then UserForm1.Label1.Caption = Target.Address(external:=True) End If End Sub I put this behind the userform: Option Explicit Private Sub UserForm_Initialize() FormIsRunning = True End Sub Private Sub UserForm_Terminate() FormIsRunning = False End Sub ======== If you're going to share this with others, don't use Personal.xls. You may end up messing up the other user's stuff. (And you share it with a workbook that's named nicely, too.) Chirs wrote: I have a Form that i display in Modeless state to allow the user make selections and changes to spreadsheets without closing the form. I would like to have the activecell value of the spreadsheet show up in a textbox on the form. i have TWO questions: 1) I am using the personal.xls file for all the forms and macros to make it available to all workbooks, will the Workbook_SheetSelectionChange of personal.xls fire for the activeworkbook AND 2) Is there a way to indiacte that the modeless form is loaded and showing to captuer the event only if it is showing at the time? -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I make excell show time values greater than 24 hrs? | Excel Worksheet Functions | |||
make Pivot table show only available values in dropdowns | Excel Discussion (Misc queries) | |||
create excel spreadsheet to display data from filled text forms | Excel Discussion (Misc queries) | |||
I need to have only the values show in a spreadsheet. | Excel Worksheet Functions | |||
Hide/Show modeless userform when activating/deactivating workbooks | Excel Programming |