How would I make a user form that floats across workbook

[HelpShaneExcel]

Hi friends @Chandoo

I have received a lot of great help here before, and am looking to you guys again. I am actually taking the time to educate myself and learn excel through youtube videos and website tutorials that I'm following along with, and so I am trying. However, there are so many ways excel can go that I fear it's going to take me a long time before I know what to do what I'd like to accomplish at work here.

I work for a mining company and we have a workbook that has a bunch of sheets with company names on them. (Contractors who come to work on our site)

Every year they have to have a certification called an 'MSHA', which is good for a year, plus to the end of month they took it, so for instance if I took mine today 11/15/2017, it would be good not only until next year (the 15th) but all of November.

Now when somebody's yearly MSHA is about to expire, we go in and manually highlight the cell red, and then make it normal again when we receive their new MSHA.

We both enter new employees under their company, as well as edit / update their info, especially these MSHA.

I am hoping to do several things here:

1> Make a userform that 'floats' over the entire workbook instead of putting the codes in for these hundreds (if not maybe even a thousand plus companies or more)

2> Have it so that the workbook and sheets are always automatically coloring themselves red when their MSHA's expire and uncoloring themselves when these dates are updated.

3> Have a button that automatically alphabetizes all the new entries instead of manually putting them in.

For the first request>

I found an excellent video (by 'Tiger Solutions') on making user forms that receive cell data, as well as being able to edit it. But if possible I would like to make it so that this Userform 'floats' over the entire workbook so that information entered in it will go into the 'activated' page of the company we are adding somebody to or updating.

For the second request>

I'd like to have the workbook always checking the pages and auto-coloring the cells red when they are within the last month of expiration or beyond that month, and then that makes their color grey again when the dates are updated.

And for the third request>

Following tutorials (again from Tiger Solutions youtube videos) I was able to make a button that calculates how many entries exist on a page, and then automatically alphabetizes them. But like the first request I'd like this to somehow 'float' over the entire workbook and auto-alphabetize the active page instead of having to make a button and code for each page individually.

Like I said I am educating myself and love what I am already learning. (It is a lot of fun) But I fear it could be a year before I became familiar enough with what I'm doing before knowing how to even make something like this happen, so any help and pointing into a certain direction would be very much appreciated!

Thanks so much my friends and if you can help with anything please do not feel like you have to help with all, as I'd be happy to have any knowledge or advice as to where to start looking, even if it does not cover all these requests and only covers one of them.

 

[Logit]

Request Answers:

#1 - A User Form can be loaded Modal or Non-Modal. These settings are in the VBE (where you enter the VBA code) in the PROPERTIES window. This window is normally on the left side of the screen. You will display the form in the design mode within the VBE, click on the form, then view the PROPERTIES on the left side. The setting you are seeking is SHOW FORM - there are several choices to the right of that setting (a drop down). Choose Non-Modal.

#2 - Auto coloring the various cells can be accomplished via CONDITIONAL FORMATTING, or you can use VBA. For all of these requests, if you search the Internet you will find a bunch of resources that walk you through the process.

#3 - Auto alphabetizing can be accomplished via VBA, placing the macro code in the Worksheet level module.

This macro sorts Column A in alphabetical order, ascending :

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        Dim lr As Long
        lr = Range("A" & Rows.Count).End(xlUp).Row
        Range("A1:A" & lr).Sort Key1:=Range("A1"), Order1:=xlAscending
    End If
End Sub

You can change to another column to sort on by editing the Range.

I believe once you get started .. posting what you have and asking for specific help on a specific problem ... Forum member will be happy to assist.

 

[HelpShaneExcel]

Logit thanks so much for the reply my friend! I was gone for a while (I usually catch up on my spread sheet stuff between rotations of shifts when I can continue working with them here at work) But thanks so much for pointing me into some helpful directions and I'm going to start looking into these solutions now! :D

 

[HelpShaneExcel]

Logit for #3 when you said, "Auto alphabetizing can be accomplished via VBA, placing the macro code in the Worksheet level module."

Do you mean place the above code in each and every different worksheet within the entire workbook? Or is this something that can be done once which would apply throughout to all tabs and worksheets which each represent their own contracting company.

Also, if I had the first rows say up to 12 that were used for company data before the rows started at 13 under employees, how would I make the above code work for that and not effect the first chunk of rows?

 

[Logit]

Range("A13:A" & lr).Sort Key1:=Range("A1"), Order1:=xlAscending

Change the above line as indicated.

Will need to be placed in each sheet level module.