SOLVED

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

Brass Contributor

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 Johnson

Maria Johnson

Mother: Anna

Baptism: 1769 at St. Andrew's, Dublin

Father: Alexandri Johnson

Margareta Johnson

Mother: Maria

Baptism: 1747 at St. Andrew's, Dublin

 

These two 'record' blocks happen to be sitting in rows 91 through 94 and columns D and E BUT the eventual data (possibly hundreds of blocks of 4) could need to be entered in any four contiguous cells.

 

As you can see much of the content is similar, and it is that fact that I was hoping to address by creating a 'dynamic' user form.

 

Basically I want to be able to click (or maybe use a special key sequence such as Shift Alt {some letter}) in a cell, the form then pop up, I then enter the variable data and then, when I press Enter, the 'record' populates the four cells, with the entered data, plus the constant text, the top left of which being the one where I clicked / special keyed.

 

This is like entering a new record in a set of records with a common set of headings BUT as the top left corner may change, from 'record' to 'record', I am unable to use the 'standard' form and new record approach.

 

Any ideas, please.

 

Best regards

Philip
Bendigo, Victoria
Australia

10 Replies

@PMHunt1955 I would advise you NOT to combine the constant text with the actual information in one single cell. It will make your life a whole lot easier if you create a simple table with these 5 headings:
First name   Last Name   Father   Mother   Baptism
and beneath this header row you enter one row of data per person. See attached.

@Jan Karel Pieterse 

Dear Jan

 

Whilst I know that your approach would be simpler, there are a whole load of reasons why I want to enter the data in the way that I have described.  I am an intermediate level VBA programmer so I am quite happy to write the necessary code, but I was just hoping for some guidance.

 

Best

 

Philip

@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

@Jan Karel Pieterse 

 

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

 

best response confirmed by PMHunt1955 (Brass Contributor)
Solution

@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

 

 

 

@Jan Karel Pieterse 

 

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

@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).

Insert code button.jpgInsert code dialog.jpg

@Jan Karel Pieterse 

 

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'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

@PMHunt1955 I honestly have no idea what causes your buttons to be less in number than mine.

@Jan Karel Pieterse 

 

Thank you for that.  Hopefully a moderator may see this and suggest why.  Anyway, thank you once again for all your help.

 

Best regards

 

Philip

1 best response

Accepted Solutions
best response confirmed by PMHunt1955 (Brass Contributor)
Solution

@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

 

 

 

View solution in original post