Vundra
02-08-2017, 10:13 AM
I'm trying to create an addin in Excel 2007 for managing comments.
This addin opens an UserForm with many command & option buttons.
The "XlBuiltinDialog" constant for the entire "Format comment" dialog (8 tabs) unfortunately doesn't exist (are available separately only 381, 207, 204 for Font, Properties, Protection) so I need to add a dummy sheet (with one visible comment and imported from the addins folder where resides together my addin) to the active workbook. This dummy sheet contains also a table where in cells are stored all the values of the comments such as font, fill, border, margins , ... in other words all the available in the 8 tabs. With "Worksheet_SelectionChange" (in dummy sheet module) I'm able to update these values and store them into the registry for advanced formatting purposes (the addin retrieves them using the "GetSetting" function).
Into the dummy sheet, with "SendKeys" method (that seems to me the only posssible solution for doing so), I'm able to open the entire "Format comment" dialog and the code for doing this is:
Sheets("dummy").Shapes("Comment 1").Select
SendKeys "^o"
The addin UserForm cannot coexist with the Excel "Format comment" dialog so I'm obliged to hide the UserForm before running the SendKeys.
All works really fine (apart some soluble registry issues related to local language, i.e "SaveSettings" function stores "Falso" instead of "False") but the great problem is that I need (after the saving procedure) to show again the UserForm directly from the dummy sheet.
There is a code for this? ... in other words, is possible to show the UserForm of another Excel file?
ONE FINAL PRECISATION: Another possible great and elegant solution is to catch the event rappresented by the OK button press on the Excel "Format comment" dialog (8 tabs) but this can be done only for the XlBuiltinDialogs and I don't know what to do in my case.
In example if you place the following code into a command button into an UserForm, the UserForm remains visible.
Private Sub CommandButton1_Click()
Dim BUTTON_OK As Boolean
Set wb = Workbooks.add(xlWorksheet)
With wb.Worksheets(1).Range("A1")
.AddComment
.Comment.Text Text:="ABC"
.Comment.Visible = True
.Comment.Shape.Select True
End With
BUTTON_OK = Application.Dialogs(xlDialogFontProperties).Show
'BUTTON_OK = Application.Dialogs(381).Show 'alternative
With Selection
cmtFontName = .Characters.Font.Name
cmtFontStyle = .Characters.Font.FontStyle
cmtFontSize = .Characters.Font.Size
cmtFontColorIndex = .Characters.Font.ColorIndex
cmtFontUnderline = .Characters.Font.Underline
cmtFontStrikethrough = .Characters.Font.Strikethrough
cmtFontSuperscript = .Characters.Font.Superscript
cmtFontSubscript = .Characters.Font.Subscript
End With
If BUTTON_OK Then
Range("A3") = cmtFontName
Range("A4") = cmtFontStyle
Range("A5") = cmtFontSize
Range("A6") = cmtFontColorIndex
Range("A7") = cmtFontUnderline
Range("A8") = cmtFontStrikethrough
Range("A9") = cmtFontSuperscript
Range("A10") = cmtFontSubscript
End If
'wb.Close False 'just to have the time to see the values
Set wb = Nothing
End Sub
I really need your precious help for terminating my project.
Many, many Thanks! :help
This addin opens an UserForm with many command & option buttons.
The "XlBuiltinDialog" constant for the entire "Format comment" dialog (8 tabs) unfortunately doesn't exist (are available separately only 381, 207, 204 for Font, Properties, Protection) so I need to add a dummy sheet (with one visible comment and imported from the addins folder where resides together my addin) to the active workbook. This dummy sheet contains also a table where in cells are stored all the values of the comments such as font, fill, border, margins , ... in other words all the available in the 8 tabs. With "Worksheet_SelectionChange" (in dummy sheet module) I'm able to update these values and store them into the registry for advanced formatting purposes (the addin retrieves them using the "GetSetting" function).
Into the dummy sheet, with "SendKeys" method (that seems to me the only posssible solution for doing so), I'm able to open the entire "Format comment" dialog and the code for doing this is:
Sheets("dummy").Shapes("Comment 1").Select
SendKeys "^o"
The addin UserForm cannot coexist with the Excel "Format comment" dialog so I'm obliged to hide the UserForm before running the SendKeys.
All works really fine (apart some soluble registry issues related to local language, i.e "SaveSettings" function stores "Falso" instead of "False") but the great problem is that I need (after the saving procedure) to show again the UserForm directly from the dummy sheet.
There is a code for this? ... in other words, is possible to show the UserForm of another Excel file?
ONE FINAL PRECISATION: Another possible great and elegant solution is to catch the event rappresented by the OK button press on the Excel "Format comment" dialog (8 tabs) but this can be done only for the XlBuiltinDialogs and I don't know what to do in my case.
In example if you place the following code into a command button into an UserForm, the UserForm remains visible.
Private Sub CommandButton1_Click()
Dim BUTTON_OK As Boolean
Set wb = Workbooks.add(xlWorksheet)
With wb.Worksheets(1).Range("A1")
.AddComment
.Comment.Text Text:="ABC"
.Comment.Visible = True
.Comment.Shape.Select True
End With
BUTTON_OK = Application.Dialogs(xlDialogFontProperties).Show
'BUTTON_OK = Application.Dialogs(381).Show 'alternative
With Selection
cmtFontName = .Characters.Font.Name
cmtFontStyle = .Characters.Font.FontStyle
cmtFontSize = .Characters.Font.Size
cmtFontColorIndex = .Characters.Font.ColorIndex
cmtFontUnderline = .Characters.Font.Underline
cmtFontStrikethrough = .Characters.Font.Strikethrough
cmtFontSuperscript = .Characters.Font.Superscript
cmtFontSubscript = .Characters.Font.Subscript
End With
If BUTTON_OK Then
Range("A3") = cmtFontName
Range("A4") = cmtFontStyle
Range("A5") = cmtFontSize
Range("A6") = cmtFontColorIndex
Range("A7") = cmtFontUnderline
Range("A8") = cmtFontStrikethrough
Range("A9") = cmtFontSuperscript
Range("A10") = cmtFontSubscript
End If
'wb.Close False 'just to have the time to see the values
Set wb = Nothing
End Sub
I really need your precious help for terminating my project.
Many, many Thanks! :help