Macro prompts and user inputs

Hello aboveliquidice,

The attached workbook is far from being comprehensive for your needs, but it does demonstrate the basic approach to your problem. VBA UserForms are by far the best way to manipulate data in database.

After you open the workbook, click the button on "Sheet 3" to activate the form. You can then add a new patient worksheet using the names you have entered. When the sheet prints, the patient's name is in Arial 24 Point bold type and centered on the page. You change the font and size to what you want to use. They are highlighted in red in code below.

Here is the code behind the UserForm...

Private Sub CommandButton1_Click()
 'CLOSE USERFORM
   Me.Hide
   Unload Me
End Sub

Private Sub CommandButton2_Click()
 'ADD NEW PATIENT WORKSHEET
 
   Dim PatientName As String
   Dim Wks As Worksheet
     
     'Patient's name is last name first
      PatientName = TextBox2 & ", " & TextBox1
      
     'Check If Patient Sheet exists
      For Each Wks In Worksheets
        If Wks.Name = PatientName Then
          MsgBox "A sheet already exists for " & PatientName
          Exit Sub
        End If
      Next Wks
      
      With Worksheets
       'Add the new worksheet
        Set Wks = .Add(After:=Worksheets(.Count))
       'Name it for the patient
        Wks.Name = PatientName
       'Set the Center Header to Arial 24 point, centered, bold
        Wks.PageSetup.CenterHeader = "&""Arial"" &C &B &24" & PatientName
      End With
      
End Sub

Private Sub UserForm_Activate()
  TextBox1.SetFocus
End Sub

Hello aboveliquidice,

To see and change the UserForm code, you need to activate the Visual Basic Editor.

1. Open the workbook and press Alt+F11 to open the VBE
2. In the upper left corner of the VBE you should see a window titled Project. If not press Ctrl+R
3. Look for the Forms folder in the Project window. If it isn't open then click on the "plus sign" to left of the folder.
4. Left Click UserForm1 to select it.
5. Press F7 to view the code.

Hey Leith,

I did manage to find the code - I then transposed the coding for the recorded "new sheet" code into the UserForm. I had no idea code could live in the form instead. Very neat.

I now have the codes merged such that clicking the "new patient" button on the main directory page prompts for last and first (in the same manner in which you created the UserForm). With this data - it creates the new sheet in perfect format (my pre-existing macro) with the name of the patient now in the correct place, and the sheet renamed to the patient.

Thank you so much for helping me get this far.

Did you have any advice for the creation of a table utilizing the PatientName variable from the input? What I am thinking is building a simple table that has a list of all sheets in the spreadsheet excluding the directory sheet. Then automatically hyperlinking to each sheet. There are other columns of the table that will require manual input - so only one column would need to be in the code / automated...

What do you think?

EDIT:

I located a nice little macro from a message board to find all the sheets and output them to a message box.

Sub List_sheets()
    Dim s As String
    s = ""
    For Each w In Worksheets
    s = s & Chr(10) & w.Name
    Next
    MsgBox (s)
End Sub

After the Next command, I could remove the output to MsgBox for (s) and instead define the table range and fill in correct?

Hello aboveliquidice,

Hyperlinks are fine if you want to jump to another program or have data that is stable in a document. In your case, patient sheets could be removed as well as added creating more more work to maintain the list. You can create a list of the all the patients on a UserForm and activate the selected sheet that way. The list will always reflect the worksheets that are in the workbook. Using the form to do this, provides the user with the freedom to move easily through hundreds or even thousands of patient sheets. The form can be displayed by user either using a keyboard shortcut or clicking a button.

Here is the code for the UserForm in the attached file. The sheet "Main" is not included in the list of worksheets. You can activate the macro by clicking the button or use the shortcut Ctrl+Shift+W.

Private Sub CommandButton1_Click()
 'CLOSE USERFORM
   Me.Hide
   Unload Me
End Sub

Private Sub CommandButton2_Click()
 'GO TO SHEET
 
   With ComboBox1
     If .ListIndex > -1 Then
       Worksheets(.List(.ListIndex)).Activate
     End If
   End With
   
End Sub

Private Sub UserForm_Initialize()

  Dim Wks As Worksheet
  
   'Load the ComboBox with the worksheets except for Main
    For Each Wks In Worksheets
      If Wks.Name <> "Main" Then
        ComboBox1.AddItem Wks.Name
      End If
    Next Wks
    
End Sub