Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Message or input
Question 1 is it possible to change a vbyesnocancel message box to a set of
captioned titles that display "USD"= true or "GBP"= false Question 2 if not how can i design something to do so. What i am ultimately looking for is : Press "Command button for a report" secondary option as a message box for "Which Currency Either USD or GBP" Click either "USD" or "GBP" and be sent to respective page. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Message or input
Hi,
Unfortunately, you can't change the text on the buttons. However, I normally amend the question to a closed one (ie...would you like to repor in USD?) or in the VBA editor instead of inserting a module, insert a user form and design it from scratch. HTH Simon thesaxonuk wrote: Question 1 is it possible to change a vbyesnocancel message box to a set of captioned titles that display "USD"= true or "GBP"= false Question 2 if not how can i design something to do so. What i am ultimately looking for is : Press "Command button for a report" secondary option as a message box for "Which Currency Either USD or GBP" Click either "USD" or "GBP" and be sent to respective pag -- -------------------- Simon - UK Email at simon22mports [ a t ] hot mail [ d ot ]com Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Message or input
Option Explicit
Private Declare Function GetCurrentThreadId Lib "kernel32" _ () As Long Public Declare Function GetDesktopWindow Lib "user32" _ () As Long Private Declare Function GetWindowLong Lib "user32" _ Alias "GetWindowLongA" _ (ByVal hWnd As Long, _ ByVal nIndex As Long) As Long Private Declare Function MessageBox Lib "user32" _ Alias "MessageBoxA" _ (ByVal hWnd As Long, _ ByVal lpText As String, _ ByVal lpCaption As String, _ ByVal wType As Long) As Long Private Declare Function SetDlgItemText Lib "user32" _ Alias "SetDlgItemTextA" _ (ByVal hDlg As Long, _ ByVal nIDDlgItem As Long, _ ByVal lpString As String) As Long Private Declare Function SetWindowsHookEx Lib "user32" _ Alias "SetWindowsHookExA" _ (ByVal idHook As Long, _ ByVal lpfn As Long, _ ByVal hmod As Long, _ ByVal dwThreadId As Long) As Long Private Declare Function SetWindowText Lib "user32" _ Alias "SetWindowTextA" _ (ByVal hWnd As Long, _ ByVal lpString As String) As Long Private Declare Function UnhookWindowsHookEx Lib "user32" _ (ByVal hHook As Long) As Long Private Const IDPROMPT = &HFFFF& Private Const WH_CBT = 5 Private Const GWL_HINSTANCE = (-6) Private Const HCBT_ACTIVATE = 5 Private Type MSGBOX_HOOK_PARAMS hWndOwner As Long hHook As Long End Type Private MSGHOOK As MSGBOX_HOOK_PARAMS Dim mbFlags As VbMsgBoxStyle Dim mbFlags2 As VbMsgBoxStyle Dim mTitle As String Dim mPrompt As String Dim But1 As String Dim But2 As String Dim But3 As String '--------------------------------------------------------------------------- Public Function cMsgBox(hWnd As Long, _ mMsgbox As VbMsgBoxStyle, _ Title As String, _ Prompt As String, _ Optional mMsgIcon As VbMsgBoxStyle, _ Optional Button1 As String, _ Optional Button2 As String, _ Optional Button3 As String) As String '--------------------------------------------------------------------------- ' Function: Controls the display of the custom MsgBox and returns the ' selected button ' Synopsis: Sets supplied custom parameters and returns text of ' the button that was pressed as a string '--------------------------------------------------------------------------- Dim mReturn As Long mbFlags = mMsgbox mbFlags2 = mMsgIcon mTitle = Title mPrompt = Prompt But1 = Button1 But2 = Button2 But3 = Button3 'show the custom messagebox mReturn = MessageBoxH(hWnd, GetDesktopWindow(), mbFlags Or mbFlags2) 'test which button of the 7 possible options has been pressed Select Case mReturn Case vbAbort cMsgBox = But1 Case vbRetry cMsgBox = But2 Case vbIgnore cMsgBox = But3 Case vbYes cMsgBox = But1 Case vbNo cMsgBox = But2 Case vbCancel cMsgBox = But3 Case vbOK cMsgBox = But1 End Select End Function '--------------------------------------------------------------------------- ---- Public Function MessageBoxH(hWndThreadOwner As Long, _ hWndOwner As Long, _ mbFlags As VbMsgBoxStyle) As Long '--------------------------------------------------------------------------- ---- ' Function: Calls the hook '--------------------------------------------------------------------------- ---- Dim hInstance As Long Dim hThreadId As Long hInstance = GetWindowLong(hWndThreadOwner, GWL_HINSTANCE) hThreadId = GetCurrentThreadId() With MSGHOOK .hWndOwner = hWndOwner .hHook = SetWindowsHookEx(WH_CBT, AddressOf MsgBoxHookProc, hInstance, hThreadId) End With MessageBoxH = MessageBox(hWndOwner, Space$(120), Space$(120), mbFlags) End Function '--------------------------------------------------------------------------- ---- Public Function MsgBoxHookProc(ByVal uMsg As Long, _ ByVal wParam As Long, _ ByVal lParam As Long) As Long '--------------------------------------------------------------------------- ---- 'Function: Formats and shows the custom messagebox ' Synopsis: Setups the window text ' Setups the dialog box text ' Checks which buttons have been added to messagebox (choice of 6 ' combinations ofthe 7 buttons), then sets the button text ' accordingly ' Then removes the hook '--------------------------------------------------------------------------- ---- If uMsg = HCBT_ACTIVATE Then SetWindowText wParam, mTitle SetDlgItemText wParam, IDPROMPT, mPrompt Select Case mbFlags Case vbAbortRetryIgnore SetDlgItemText wParam, vbAbort, But1 SetDlgItemText wParam, vbRetry, But2 SetDlgItemText wParam, vbIgnore, But3 Case vbYesNoCancel SetDlgItemText wParam, vbYes, But1 SetDlgItemText wParam, vbNo, But2 SetDlgItemText wParam, vbCancel, But3 Case vbOKOnly SetDlgItemText wParam, vbOK, But1 Case vbRetryCancel SetDlgItemText wParam, vbRetry, But1 SetDlgItemText wParam, vbCancel, But2 Case vbYesNo SetDlgItemText wParam, vbYes, But1 SetDlgItemText wParam, vbNo, But2 Case vbOKCancel SetDlgItemText wParam, vbOK, But1 SetDlgItemText wParam, vbCancel, But2 End Select UnhookWindowsHookEx MSGHOOK.hHook End If MsgBoxHookProc = False End Function Sub Test() Dim mReturn As String mReturn = cMsgBox(1, _ vbYesNoCancel, _ "Customize your message box buttons", _ "Do you not agree that this is pretty cool?", _ , _ "I Like It", _ "Not For Me", _ "I;ll Get Back To You") cMsgBox 1, _ vbOKOnly, _ "Customize your message box buttons", _ "You selected the button captioned: " & vbCrLf & mReturn, _ , _ "Okay" End Sub Sub test2() Dim a Dim ary ary = Array("vbOKOnly", "vbOK", "vbCancel", "vbAbort", "vbRetry", "vbIgnore", "vbYes", "vbNo") a = MsgBox("Hello", vbAbortRetryIgnore) MsgBox (ary(a)) End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "smw226 via OfficeKB.com" <u27645@uwe wrote in message news:6841a4808358a@uwe... Hi, Unfortunately, you can't change the text on the buttons. However, I normally amend the question to a closed one (ie...would you like to repor in USD?) or in the VBA editor instead of inserting a module, insert a user form and design it from scratch. HTH Simon thesaxonuk wrote: Question 1 is it possible to change a vbyesnocancel message box to a set of captioned titles that display "USD"= true or "GBP"= false Question 2 if not how can i design something to do so. What i am ultimately looking for is : Press "Command button for a report" secondary option as a message box for "Which Currency Either USD or GBP" Click either "USD" or "GBP" and be sent to respective pag -- -------------------- Simon - UK Email at simon22mports [ a t ] hot mail [ d ot ]com Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Message or input
Bob,
Being a bit slow here where would i put the code below? "Bob Phillips" wrote: Option Explicit Private Declare Function GetCurrentThreadId Lib "kernel32" _ () As Long Public Declare Function GetDesktopWindow Lib "user32" _ () As Long Private Declare Function GetWindowLong Lib "user32" _ Alias "GetWindowLongA" _ (ByVal hWnd As Long, _ ByVal nIndex As Long) As Long Private Declare Function MessageBox Lib "user32" _ Alias "MessageBoxA" _ (ByVal hWnd As Long, _ ByVal lpText As String, _ ByVal lpCaption As String, _ ByVal wType As Long) As Long Private Declare Function SetDlgItemText Lib "user32" _ Alias "SetDlgItemTextA" _ (ByVal hDlg As Long, _ ByVal nIDDlgItem As Long, _ ByVal lpString As String) As Long Private Declare Function SetWindowsHookEx Lib "user32" _ Alias "SetWindowsHookExA" _ (ByVal idHook As Long, _ ByVal lpfn As Long, _ ByVal hmod As Long, _ ByVal dwThreadId As Long) As Long Private Declare Function SetWindowText Lib "user32" _ Alias "SetWindowTextA" _ (ByVal hWnd As Long, _ ByVal lpString As String) As Long Private Declare Function UnhookWindowsHookEx Lib "user32" _ (ByVal hHook As Long) As Long Private Const IDPROMPT = &HFFFF& Private Const WH_CBT = 5 Private Const GWL_HINSTANCE = (-6) Private Const HCBT_ACTIVATE = 5 Private Type MSGBOX_HOOK_PARAMS hWndOwner As Long hHook As Long End Type Private MSGHOOK As MSGBOX_HOOK_PARAMS Dim mbFlags As VbMsgBoxStyle Dim mbFlags2 As VbMsgBoxStyle Dim mTitle As String Dim mPrompt As String Dim But1 As String Dim But2 As String Dim But3 As String '--------------------------------------------------------------------------- Public Function cMsgBox(hWnd As Long, _ mMsgbox As VbMsgBoxStyle, _ Title As String, _ Prompt As String, _ Optional mMsgIcon As VbMsgBoxStyle, _ Optional Button1 As String, _ Optional Button2 As String, _ Optional Button3 As String) As String '--------------------------------------------------------------------------- ' Function: Controls the display of the custom MsgBox and returns the ' selected button ' Synopsis: Sets supplied custom parameters and returns text of ' the button that was pressed as a string '--------------------------------------------------------------------------- Dim mReturn As Long mbFlags = mMsgbox mbFlags2 = mMsgIcon mTitle = Title mPrompt = Prompt But1 = Button1 But2 = Button2 But3 = Button3 'show the custom messagebox mReturn = MessageBoxH(hWnd, GetDesktopWindow(), mbFlags Or mbFlags2) 'test which button of the 7 possible options has been pressed Select Case mReturn Case vbAbort cMsgBox = But1 Case vbRetry cMsgBox = But2 Case vbIgnore cMsgBox = But3 Case vbYes cMsgBox = But1 Case vbNo cMsgBox = But2 Case vbCancel cMsgBox = But3 Case vbOK cMsgBox = But1 End Select End Function '--------------------------------------------------------------------------- ---- Public Function MessageBoxH(hWndThreadOwner As Long, _ hWndOwner As Long, _ mbFlags As VbMsgBoxStyle) As Long '--------------------------------------------------------------------------- ---- ' Function: Calls the hook '--------------------------------------------------------------------------- ---- Dim hInstance As Long Dim hThreadId As Long hInstance = GetWindowLong(hWndThreadOwner, GWL_HINSTANCE) hThreadId = GetCurrentThreadId() With MSGHOOK .hWndOwner = hWndOwner .hHook = SetWindowsHookEx(WH_CBT, AddressOf MsgBoxHookProc, hInstance, hThreadId) End With MessageBoxH = MessageBox(hWndOwner, Space$(120), Space$(120), mbFlags) End Function '--------------------------------------------------------------------------- ---- Public Function MsgBoxHookProc(ByVal uMsg As Long, _ ByVal wParam As Long, _ ByVal lParam As Long) As Long '--------------------------------------------------------------------------- ---- 'Function: Formats and shows the custom messagebox ' Synopsis: Setups the window text ' Setups the dialog box text ' Checks which buttons have been added to messagebox (choice of 6 ' combinations ofthe 7 buttons), then sets the button text ' accordingly ' Then removes the hook '--------------------------------------------------------------------------- ---- If uMsg = HCBT_ACTIVATE Then SetWindowText wParam, mTitle SetDlgItemText wParam, IDPROMPT, mPrompt Select Case mbFlags Case vbAbortRetryIgnore SetDlgItemText wParam, vbAbort, But1 SetDlgItemText wParam, vbRetry, But2 SetDlgItemText wParam, vbIgnore, But3 Case vbYesNoCancel SetDlgItemText wParam, vbYes, But1 SetDlgItemText wParam, vbNo, But2 SetDlgItemText wParam, vbCancel, But3 Case vbOKOnly SetDlgItemText wParam, vbOK, But1 Case vbRetryCancel SetDlgItemText wParam, vbRetry, But1 SetDlgItemText wParam, vbCancel, But2 Case vbYesNo SetDlgItemText wParam, vbYes, But1 SetDlgItemText wParam, vbNo, But2 Case vbOKCancel SetDlgItemText wParam, vbOK, But1 SetDlgItemText wParam, vbCancel, But2 End Select UnhookWindowsHookEx MSGHOOK.hHook End If MsgBoxHookProc = False End Function Sub Test() Dim mReturn As String mReturn = cMsgBox(1, _ vbYesNoCancel, _ "Customize your message box buttons", _ "Do you not agree that this is pretty cool?", _ , _ "I Like It", _ "Not For Me", _ "I;ll Get Back To You") cMsgBox 1, _ vbOKOnly, _ "Customize your message box buttons", _ "You selected the button captioned: " & vbCrLf & mReturn, _ , _ "Okay" End Sub Sub test2() Dim a Dim ary ary = Array("vbOKOnly", "vbOK", "vbCancel", "vbAbort", "vbRetry", "vbIgnore", "vbYes", "vbNo") a = MsgBox("Hello", vbAbortRetryIgnore) MsgBox (ary(a)) End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "smw226 via OfficeKB.com" <u27645@uwe wrote in message news:6841a4808358a@uwe... Hi, Unfortunately, you can't change the text on the buttons. However, I normally amend the question to a closed one (ie...would you like to repor in USD?) or in the VBA editor instead of inserting a module, insert a user form and design it from scratch. HTH Simon thesaxonuk wrote: Question 1 is it possible to change a vbyesnocancel message box to a set of captioned titles that display "USD"= true or "GBP"= false Question 2 if not how can i design something to do so. What i am ultimately looking for is : Press "Command button for a report" secondary option as a message box for "Which Currency Either USD or GBP" Click either "USD" or "GBP" and be sent to respective pag -- -------------------- Simon - UK Email at simon22mports [ a t ] hot mail [ d ot ]com Message posted via http://www.officekb.com |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Message or input
In a standard code module. There is an example macro at the end to
demonstrate it. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "thesaxonuk" wrote in message ... Bob, Being a bit slow here where would i put the code below? "Bob Phillips" wrote: Option Explicit Private Declare Function GetCurrentThreadId Lib "kernel32" _ () As Long Public Declare Function GetDesktopWindow Lib "user32" _ () As Long Private Declare Function GetWindowLong Lib "user32" _ Alias "GetWindowLongA" _ (ByVal hWnd As Long, _ ByVal nIndex As Long) As Long Private Declare Function MessageBox Lib "user32" _ Alias "MessageBoxA" _ (ByVal hWnd As Long, _ ByVal lpText As String, _ ByVal lpCaption As String, _ ByVal wType As Long) As Long Private Declare Function SetDlgItemText Lib "user32" _ Alias "SetDlgItemTextA" _ (ByVal hDlg As Long, _ ByVal nIDDlgItem As Long, _ ByVal lpString As String) As Long Private Declare Function SetWindowsHookEx Lib "user32" _ Alias "SetWindowsHookExA" _ (ByVal idHook As Long, _ ByVal lpfn As Long, _ ByVal hmod As Long, _ ByVal dwThreadId As Long) As Long Private Declare Function SetWindowText Lib "user32" _ Alias "SetWindowTextA" _ (ByVal hWnd As Long, _ ByVal lpString As String) As Long Private Declare Function UnhookWindowsHookEx Lib "user32" _ (ByVal hHook As Long) As Long Private Const IDPROMPT = &HFFFF& Private Const WH_CBT = 5 Private Const GWL_HINSTANCE = (-6) Private Const HCBT_ACTIVATE = 5 Private Type MSGBOX_HOOK_PARAMS hWndOwner As Long hHook As Long End Type Private MSGHOOK As MSGBOX_HOOK_PARAMS Dim mbFlags As VbMsgBoxStyle Dim mbFlags2 As VbMsgBoxStyle Dim mTitle As String Dim mPrompt As String Dim But1 As String Dim But2 As String Dim But3 As String '--------------------------------------------------------------------------- Public Function cMsgBox(hWnd As Long, _ mMsgbox As VbMsgBoxStyle, _ Title As String, _ Prompt As String, _ Optional mMsgIcon As VbMsgBoxStyle, _ Optional Button1 As String, _ Optional Button2 As String, _ Optional Button3 As String) As String '--------------------------------------------------------------------------- ' Function: Controls the display of the custom MsgBox and returns the ' selected button ' Synopsis: Sets supplied custom parameters and returns text of ' the button that was pressed as a string '--------------------------------------------------------------------------- Dim mReturn As Long mbFlags = mMsgbox mbFlags2 = mMsgIcon mTitle = Title mPrompt = Prompt But1 = Button1 But2 = Button2 But3 = Button3 'show the custom messagebox mReturn = MessageBoxH(hWnd, GetDesktopWindow(), mbFlags Or mbFlags2) 'test which button of the 7 possible options has been pressed Select Case mReturn Case vbAbort cMsgBox = But1 Case vbRetry cMsgBox = But2 Case vbIgnore cMsgBox = But3 Case vbYes cMsgBox = But1 Case vbNo cMsgBox = But2 Case vbCancel cMsgBox = But3 Case vbOK cMsgBox = But1 End Select End Function '--------------------------------------------------------------------------- ---- Public Function MessageBoxH(hWndThreadOwner As Long, _ hWndOwner As Long, _ mbFlags As VbMsgBoxStyle) As Long '--------------------------------------------------------------------------- ---- ' Function: Calls the hook '--------------------------------------------------------------------------- ---- Dim hInstance As Long Dim hThreadId As Long hInstance = GetWindowLong(hWndThreadOwner, GWL_HINSTANCE) hThreadId = GetCurrentThreadId() With MSGHOOK .hWndOwner = hWndOwner .hHook = SetWindowsHookEx(WH_CBT, AddressOf MsgBoxHookProc, hInstance, hThreadId) End With MessageBoxH = MessageBox(hWndOwner, Space$(120), Space$(120), mbFlags) End Function '--------------------------------------------------------------------------- ---- Public Function MsgBoxHookProc(ByVal uMsg As Long, _ ByVal wParam As Long, _ ByVal lParam As Long) As Long '--------------------------------------------------------------------------- ---- 'Function: Formats and shows the custom messagebox ' Synopsis: Setups the window text ' Setups the dialog box text ' Checks which buttons have been added to messagebox (choice of 6 ' combinations ofthe 7 buttons), then sets the button text ' accordingly ' Then removes the hook '--------------------------------------------------------------------------- ---- If uMsg = HCBT_ACTIVATE Then SetWindowText wParam, mTitle SetDlgItemText wParam, IDPROMPT, mPrompt Select Case mbFlags Case vbAbortRetryIgnore SetDlgItemText wParam, vbAbort, But1 SetDlgItemText wParam, vbRetry, But2 SetDlgItemText wParam, vbIgnore, But3 Case vbYesNoCancel SetDlgItemText wParam, vbYes, But1 SetDlgItemText wParam, vbNo, But2 SetDlgItemText wParam, vbCancel, But3 Case vbOKOnly SetDlgItemText wParam, vbOK, But1 Case vbRetryCancel SetDlgItemText wParam, vbRetry, But1 SetDlgItemText wParam, vbCancel, But2 Case vbYesNo SetDlgItemText wParam, vbYes, But1 SetDlgItemText wParam, vbNo, But2 Case vbOKCancel SetDlgItemText wParam, vbOK, But1 SetDlgItemText wParam, vbCancel, But2 End Select UnhookWindowsHookEx MSGHOOK.hHook End If MsgBoxHookProc = False End Function Sub Test() Dim mReturn As String mReturn = cMsgBox(1, _ vbYesNoCancel, _ "Customize your message box buttons", _ "Do you not agree that this is pretty cool?", _ , _ "I Like It", _ "Not For Me", _ "I;ll Get Back To You") cMsgBox 1, _ vbOKOnly, _ "Customize your message box buttons", _ "You selected the button captioned: " & vbCrLf & mReturn, _ , _ "Okay" End Sub Sub test2() Dim a Dim ary ary = Array("vbOKOnly", "vbOK", "vbCancel", "vbAbort", "vbRetry", "vbIgnore", "vbYes", "vbNo") a = MsgBox("Hello", vbAbortRetryIgnore) MsgBox (ary(a)) End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "smw226 via OfficeKB.com" <u27645@uwe wrote in message news:6841a4808358a@uwe... Hi, Unfortunately, you can't change the text on the buttons. However, I normally amend the question to a closed one (ie...would you like to repor in USD?) or in the VBA editor instead of inserting a module, insert a user form and design it from scratch. HTH Simon thesaxonuk wrote: Question 1 is it possible to change a vbyesnocancel message box to a set of captioned titles that display "USD"= true or "GBP"= false Question 2 if not how can i design something to do so. What i am ultimately looking for is : Press "Command button for a report" secondary option as a message box for "Which Currency Either USD or GBP" Click either "USD" or "GBP" and be sent to respective pag -- -------------------- Simon - UK Email at simon22mports [ a t ] hot mail [ d ot ]com Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Prevent Duplicate Data from inputing using input application? | Excel Discussion (Misc queries) | |||
Data Valitaion Input message | Excel Worksheet Functions | |||
Clear"Reset" data input. | Excel Worksheet Functions | |||
Check box and and formula | Excel Discussion (Misc queries) | |||
How do i change the location where the input message appears | Excel Discussion (Misc queries) |