I'm currently working on a macro that extracts data from a complicated .txt file and puts it into a organized sheet. It works wonderfully, but there are some calculations I would like to do on the resulting sheet and have them displayed for the user, but the problem is, the column widths vary and to put this data at the top of the sheet is a bit unreadable because the column widths all vary. The sheet is used by the user to correct rejections from a Pharmacy Point of Sale program, so the data I want to display will help the user know how many dollars in rejections he/she has corrected as a form of feedback for the user. I'm trying to determine what would be the best way to create some sort of independent data set that the user can see while he/she uses the sheet without having to use the column widths of the sheet the user is working on. The ideas I've come up with so far:
1. Use the extensibility package and have the macro that creates the sheet create a worksheet_change event macro that does the calculations and puts them in the status bar (I'm pretty sure this will work, but I'm nervous about virus scanners seeing it as a virus). Something like this is what I'm thinking (this is just an example, the actual ranges involved and calculations will be quite a bit more extensive):
2. Create a userform with code that keeps it always on top so the user can see the updated calculations as they work on the sheet. This is a little less problematic, I suppose, but not quite a slick and nice looking as using the status bar.
3. Somehow embed a new worksheet into the current worksheet that performs the calculations and displays them (this is a similar option to that of the userform in my mind)
4. Put the calculations into a new sheet in the workbook. This is the easiest option, but the reason I don't like it as much, is that I would prefer that the calculations display for the user on the same sheet he/she is using. Nonetheless, I may go with this option just because of its simplicity.
5. I don't think this is possible, but it sure would be nice if somehow I could split the current sheet into two independent sheets with independent columns so I could display the calculations at the top of the sheet in a nicely formatted data set, and then have the user data below. I think of this kind of like in MS Word when you have one page portrait, a page break, then the next page landscape. I really only need one independent row to display the data I need to display (obviously since I could display what I need to in the status bar)
6. Lastly, I don't think this is possible either, but wouldn't it be nice if some real estate on the ribbon could be used to create a custom embedded sheet for displaying data in an independent fashion from the main sheet?
I'm sure there are options that I don't know about, and that is why I'm posting this question. Please feel free to offer other ideas, or suggest which of these options makes the most sense and why.
Thank you for your thoughts on this!!!
1. Use the extensibility package and have the macro that creates the sheet create a worksheet_change event macro that does the calculations and puts them in the status bar (I'm pretty sure this will work, but I'm nervous about virus scanners seeing it as a virus). Something like this is what I'm thinking (this is just an example, the actual ranges involved and calculations will be quite a bit more extensive):
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If WorksheetFunction.CountA(Range("T5:T8")) = 0 Or WorksheetFunction.CountA(Range("T9:T12")) = 0 _
Then Exit Sub
Application.StatusBar = "Custom Calc1: " & Application.Evaluate("=sum(T5:T8)") & _
" Custom Calc2: " & Application.Evaluate("=sum(T9:T12)") & " Custom Calc2: " & _
Application.Evaluate("=Average(T5:T12)")
End Sub2. Create a userform with code that keeps it always on top so the user can see the updated calculations as they work on the sheet. This is a little less problematic, I suppose, but not quite a slick and nice looking as using the status bar.
3. Somehow embed a new worksheet into the current worksheet that performs the calculations and displays them (this is a similar option to that of the userform in my mind)
4. Put the calculations into a new sheet in the workbook. This is the easiest option, but the reason I don't like it as much, is that I would prefer that the calculations display for the user on the same sheet he/she is using. Nonetheless, I may go with this option just because of its simplicity.
5. I don't think this is possible, but it sure would be nice if somehow I could split the current sheet into two independent sheets with independent columns so I could display the calculations at the top of the sheet in a nicely formatted data set, and then have the user data below. I think of this kind of like in MS Word when you have one page portrait, a page break, then the next page landscape. I really only need one independent row to display the data I need to display (obviously since I could display what I need to in the status bar)
6. Lastly, I don't think this is possible either, but wouldn't it be nice if some real estate on the ribbon could be used to create a custom embedded sheet for displaying data in an independent fashion from the main sheet?
I'm sure there are options that I don't know about, and that is why I'm posting this question. Please feel free to offer other ideas, or suggest which of these options makes the most sense and why.
Thank you for your thoughts on this!!!
