Forum Discussion

PMHunt1955

Brass Contributor

Jun 24, 2019

Solved

Create a pop-up form for entering current 'block' of cells

I have a block of cells that I wish to enter at various points through my spreadsheet. A couple of 'blocks' that I have already done 'manually' are as follows – Father: Christophori Johns...

excel

forms

Macros and VBA

JKPieterse
Jun 25, 2019
PMHunt1955 Hi Philip, to start with your last question: if you click the Reply button rather than the "Quick reply" button and select the Rich Text tab at the top of the small reply window, you should see a button </>. Click that to insert code.

To your other questions.

Best to put both the OnKey routine and the routine that opens the form in a normal module:

Sub ShowBaptismEntryForm() Enter_Baptism.Show End Sub Sub SetHotKey() Application.OnKey "+^b", "ShowBaptismEntryForm" End Sub Sub ResetHotKey() Application.OnKey "+^b" End Sub

And in ThisWorkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean) ResetHotKey End Sub Private Sub Workbook_Open() SetHotKey End Sub

PMHunt1955

Brass Contributor

Jun 25, 2019

JKPieterse

I have created the form (Name = Enter_Baptism), and code, but I am having difficulty in assigning the shortcut. There are in fact two issues associated therewith - i. I cannot get the shortcut to call the form and ii. I cannot assign the shortcut that I really want.

Form Code

[CODE]

Private Sub Baptism_OK_Click()

With ActiveCell

.Value = "Father: " & txt_Father.Value
.Offset(0, 1).Value = txt_Child.Value
.Offset(1, 0).Value = "Mother: " & txt_Mother.Value
.Offset(1, 1).Value = "Baptism: " & txt_Baptism.Value & " at St Andrew's, Dublin"

End With

End Sub

Private Sub cmd_Close_Click()

Unload Me

End Sub

[/CODE]

Attempt at code to cause calling up of form

[CODE]

Private Sub Show_Baptism_Entry_Form()

Enter_Baptism.Show

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.OnKey "+^{RIGHT}", "Show_Baptism_Entry_Form"

End Sub

[/CODE]

The clicking of CTRL + Shift + Right displays a message, which says that Show_Baptism_Entry_Form cannot be found.

If I click Macros in the Developer>Code part of the ribbon and type Sheet1.Show_Baptism_Entry_Form explicitly into the name field, the form then displays and acts correctly.

I have tried the line Application.OnKey "+^{RIGHT}", "Show_Baptism_Entry_Form", both with and without Sheet1. typed in before Show_Baptism_Entry_Form, neither approach works.

I really wanted the key sequence to be Ctrl+Shift+B, but I cannot get the system to recognise that at all.

Finally I must apologise for not formatting the above code examples correctly. I cannot find here on the forum a simple set of instructions regarding how to insert code in posts. I tried the Formats pull down but, though there is a code option there, I am obviously not using it correctly, as the Preview said that there was invalid HTML. With apologies for mentioning another board, it is so much simpler on MrExcel, all one does there is type [CODE] and [/CODE] either side of the code and it works, as I have shown above. I should be grateful for some similarly simple instructions as to how to achieve the same result here on this forum, please.

With thanks in anticipation.

Philip

Bendigo, Victoria

Australia

JKPieterse

Silver Contributor

Jun 25, 2019

PMHunt1955 Hi Philip, to start with your last question: if you click the Reply button rather than the "Quick reply" button and select the Rich Text tab at the top of the small reply window, you should see a button </>. Click that to insert code.

To your other questions.

Best to put both the OnKey routine and the routine that opens the form in a normal module:

Sub ShowBaptismEntryForm()
    Enter_Baptism.Show
End Sub

Sub SetHotKey()
    Application.OnKey "+^b", "ShowBaptismEntryForm"
End Sub

Sub ResetHotKey()
    Application.OnKey "+^b"
End Sub

And in ThisWorkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   ResetHotKey
End Sub

Private Sub Workbook_Open()
    SetHotKey
End Sub

PMHunt1955
Brass Contributor
Jun 25, 2019
JKPieterse

Dear Jan

Thank you for all of that. I got it to work. Just one point about the normal module, for anyone else reading this -

The normal module, where the subroutines are put, has to be a module within the xlsm workbook not one of the modules, within an xlam, where one's functions code is placed.

Re the entering of code in these posts. There is no </> button in my browser display, neither when using Firefox or Edge. There is a Formats dropdown, which includes, under the option Inline, <> Code (in red with a pink background). Clicking on that produces a pink blob, upon which, I suppose, I write code. I tried that, and all it did, when I previewed my reply, was cause the system to say the post included invalid HTML and it stripped it out.

I also tried i. clicking the pink option, then typing the code after the pink blob, which simply sent the pink blob away, and ii. typing the code and then surrounding it with that inline code, but, again, it just got stripped out with the message -

Your post has been changed because invalid HTML was found in the message body. The invalid HTML has been removed. Please review the message and submit the message when you are satisfied.

Even the copy and pasting of this message, which came up in red, and therefore pasted in red, caused the same error message to appear.

I wish I could master this one, but it has stumped me. If I could be given an exact key stroke by key stroke set of instructions then that would be great but, basically, in my experience, this board makes the posting of code, within a post, just way too difficult; Mr Excel's approach is so much easier. Dear Jan, please do not take this as directed to you; I just hope one of the board moderators sees this comment and posts such a simple set of instructions and/or changes the way that code is entered, in a post, to a much simpler method, like that found on MrExcel.

Thank you, Jan, once again for persevering with me

Best

Philip
Bendigo, Victoria
Australia
- JKPieterse
  Silver Contributor
  Jun 25, 2019
  PMHunt1955
  
  Regarding this:
  
  "The normal module, where the subroutines are put, has to be a module within the xlsm workbook not one of the modules, within an xlam, where one's functions code is placed."
  
  Not sure why you say that?
  
  To the insert code button, please see attached screen-shots (taken in Edge under Windows 10).
  - PMHunt1955
    Brass Contributor
    Jun 25, 2019
    JKPieterse
    
    Dear Jan
    
    Thank you for posting all of that. I made the comment re where the module needed to be, because I originally put the code in my xlam, where all my functions are, and the 'process' did not work. It was only when I created a Modules>Module1 within the workbook, which is the subject of this discussion, and placed the code there, in Module1, that the 'process' worked.
    
    Thank you for all those screen dumps and annotations re posting code. Unfortunately my display only includes a little less than half of the icons / options that yours does. See picture.
    
    The "top" of my forum postings Reply 'window'
    I get the same 'top' whether I am in Edge or Firefox, and I am using Windows 10 (automatic update).
    
    Best regards
    
    Philip