+ Reply to Thread
Results 1 to 4 of 4

custom form to fill in training matrix

Hybrid View

Hammer_757 custom form to fill in... 04-03-2012, 03:56 PM
rylo Re: custom form to fill in... 04-03-2012, 08:52 PM
Hammer_757 Re: custom form to fill in... 04-05-2012, 10:18 AM
rylo Re: custom form to fill in... 04-09-2012, 05:55 PM
  1. #1
    Forum Contributor
    Join Date
    03-21-2004
    Location
    Norwich, CT USA
    MS-Off Ver
    Excel 2010
    Posts
    163

    custom form to fill in training matrix

    I need to create a form for editing a training record sheet.
    The worksheet is setup with employees listed in column B and training topics listed across row 1. The cells in the grid are filled with the last date that an employee attended the corresponding topic.
    I update the worksheet (currently 20x32) from training record sheets that include name of each attendee. The names on the training records are in random order (hand written) and obviously never match the order on the worksheet.
    I would like to create a custom form that pops up when I right click on a topic that has a text box for entering a date and lists each employee with a radio button so that I can enter the date, check the employees that attended, select OK and the selected employees would be updated with the date entered on the form.

    I can handle the right click event and create the form but I not sure how to setup the code that fills in the cells based on the topic and names.
    Also I need the employee names on the form to be dynamic because people come and go...

    Any help, pointers, examples etc would be greatly appreciated.
    Robert

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: custom form to fill in training matrix

    Hi

    Some basics for you. It does a sort of the names so that you can find them more easily (but doesn't alter the order in the workbook). It only does some very basic checking on the date.

    822980.xlsm

    rylo

  3. #3
    Forum Contributor
    Join Date
    03-21-2004
    Location
    Norwich, CT USA
    MS-Off Ver
    Excel 2010
    Posts
    163

    Re: custom form to fill in training matrix

    thanks rylo, this has worked out nicely. Im having one problem that I cannot work out. I need to return the default right click menu to the cells that are not in the target range. here is the modified beforerightclick event code Im currently using:
    Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
        Dim topicRng As Range
    
        Cancel = True
        Set topicRng = Range("topics")
        
        If Not Application.Intersect(Target, topicRng) _
            Is Nothing And Target.Cells.Count = 1 Then
            UserForm1.Show
        End If
        
        Cancel = False
      
    End Sub
    I added the "Cancel = False" but it hasnt helped.
    any idea how to get the default right click to be available to the other cells?
    Thanks
    Robert

  4. #4
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: custom form to fill in training matrix

    Hi

    I did a bit of quick testing, and if you don't put anything for the cancel, then it seemed to work.

    I just built a quick form that only had a button to close the form, then in the beforerightclick event, just put in the form.show command. The form showed, then closed, then immediately went back to the default options.

    Give that a go and see if it works.

    rylo

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1