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

JKPieterse
Silver Contributor
Jun 24, 2019
PMHunt1955 If you insist on having this layout :-) I guess the best way is to insert a userform into your VBA project with the appropriate number of textboxes. It is a good idea to name each textbox after you've added it using the properties windows (View, properties). Assuming the top-left cell of a "block" is selected when the form is opened, you can then add the entries of the textboxes into the cells when the user clicks the OK button (I called it cmbOK), The code for the OK button would be something like:

Private Sub cmbOK_Click() With ActiveCell .Value = "Father: " & tbxFather.Value .Offset(0, 1).Value = tbxFirstName.Value & " " & tbxLastName.Value .Offset(1, 0).Value = "Mother: " & tbxMother.Value .Offset(1, 1).Value = "Baptism: " & tbxBaptism.Value End With 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

Forum Discussion

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

Resources