Forum Discussion

PMHunt1955's avatar
PMHunt1955
Copper 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 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

excel
forms
Macros and VBA
  • JKPieterse's avatar
    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

     

     

     

10 Replies

Sort By
  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Jun 24, 2019

    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.

    • PMHunt1955's avatar
      PMHunt1955
      Copper Contributor
      Jun 24, 2019

      JKPieterse 

      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

      • JKPieterse's avatar
        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

Resources