Posts by vwankerl

    Re: Userform outputs to multiple worksheets /w if/then or case select statements

    Here are some suggestions:

    1. In the UserForm_Initialize procedure, instead of hard-coding the team names in the ListBox, enumerate through the worksheets using the Name property to add to the list. This will ensure that the selected team name will match the sheet name.

    2. In the OKButton_Click procedure, after putting the data into the proper row in the Draft Results sheet, enumerate through the worksheets collection comparing the Team name from the form with the Name property. When you find the correct worksheet, do a Range("A:A").Find(What:="the position"). This will provide the row on the proper worksheet to copy data to. Note though that you have position codes that are duplicates. This will cause additional complexities in selecting the proper row to use on the Team worksheet. You may want to make all the duplicates into unique values (for example add a sequence number to each) to simplify the process of selecting the proper row.

    Be sure to ask questions if you don't understand.

    Re: Automatically merge worksheets for real-time inventory (look up cell value?)

    Basically what you want to do is update a database (even though it may be in the form of a spreadsheet) from a form driven spreadsheet. Very doable, but you will need some knowledge of using databases from VBA. Trying searching for information on using ADO in VBA or reading and writing databases with VBA. There is lots of information for doing this and there may even be applications available to do it off the shelf.

    Re: Sum of elements of a collection class method

    I have attached a workbook with some new coding in it. I have defined two new classes, cRoom and cRooms. cRoom is very much like the cRoomCooling class from before, but with the idea that it will be more generic to allow for Cooling, Heating, Air flow, and any other values "properties" that you may need for each room. Note that it also has a RoomKey property. That is to be a unique identifier for each object created from this class.

    The cRooms class replaces the cSystemCooling class and as with the cRoom class above is designed to be more generic for future additions/enhancements. You will see that it encapsulates (contains) the collection of cRoom objects. Note that I have added some additional properties and methods to let you work with cRooms objects as though they were collections.

    I can go into more detail about these but want to be specific to your needs and understanding so please look this over and ask questions or provide more detail in your needs so I can help you understand what I have here and how we can make it do what you want.

    Re: Sum of elements of a collection class method

    To answer your last post, a collection is nothing more than an object that contains other objects. If I understand what you are trying to do, I would suggest that you define a class cAllRooms that would contain, among other things, a collection of cRooms. You could then code the cAllRooms class so you could do a For Each objRoom in objAllRooms loop. You could also define methods and properties that might do some of what you are talking about. I will try to expand my previous post to show you what I mean.

    Re: Sum of elements of a collection class method

    I have reworked your code and logic some to maybe help you in your task.

    First, I have two classes, named the same as yours. However, the cSystemCooling class is defined a little differently. I made the assumption that you were trying to define the cSystemCooling class as an aggregate of all the cRoomCooling objects. Based on that assumption, I moved the collection that you create in your test function into the cSystemCooling class. That meant I needed to have an Add method in cSystemCooling to add cRoomCooling objects to it. Also, I added an Initialize method to cSystemCooling so that when you create a new cSystemCooling object, the class will initialize with a new collection for the cRoomCooling objects and initialize the sum of the cRoomCooling SupplyAir values to 0. The Add method will add the cRoomCooling object to the private collection and add the cRoomCooling SupplyAir value to the private total in the cSystemCooling class. To get the cSystemCooling object SupplyAir value simply use the the Property Get SupplyAir of the cSystemCooling object. Here is the code:

    This is the cRoomCooling class

    This is the cSystemCooling class

    And here is the test function.

    Now, I am not sure where you want to go with this. I did not quite understand the end of your OP and the questions you had there. If you could try these code changes out and then ask any questions you have from there I will be glad to help.

    Re: Improve Nested If VBA User-defined function

    I need some clarification. This is a UDF which means you use it by entering it into a formula in a cell, is that right?

    According to this code, you are only processing one row per function call, is that what you expect to do?

    Can you attach a sample spreadsheet (sanitized for security) to show the structure of your data? Just looking at this function I can't tell if it is even coded to do what you want.

    Re: Lock cell after specified date

    See the change I made below. A MsgBox is not a property of the Worksheet object.

    Quote from Latham;594304

    Can a msg box be added. I tried to enter a simple msg box, but I'm getting error 438

    Run Time Error 438
    Object does not suport this poperty or method.
    The cells do lock, but the error shows.

    Re: Lock cell after specified date

    What module is this code in? Is it a standard Module or a worksheet or ThisWorkbook module?

    I would use the Workbook_Open event to check the system date and do the lock. That way, there is no time when the user can access the cells prior to locking them.

    Here is some code that might do the job.

    Option Explicit
    Private Sub Workbook_Open()
        If Date >= #1/1/2013# Then
            ActiveSheet.Unprotect Password:="****"
            Range("C12:K18").Locked = True
            ActiveSheet.Protect Password:="****"
        End If
    End Sub

    Re: Create a command from a string

    No problem. I learned something new also from Wigi about the Me("OptionButton") statement. By the way, the rules for forming program statements is "Syntax", not "sintax". A sin tax is a tax on liquor or cigarettes or other such things a government imposes :).

    Keep learning and asking questions.

    Re: Create a command from a string

    I modified the above code to this thinking you probably wanted to turn off only some of the OptionButtons. This will look at each of the cells in the nameList to see if there is a value there. If there is no value, it will hide the OptionButton otherwise it will leave it visible.

    Re: Fetching cell content from hundreds of Excel files into rows of a master workbook

    I am attaching the master file you provided that I have added VBA code to so it would do what I think you want to do (short of copying all the cells you are wanting). You will need to copy the code (two standard modules) to your master file and modify as needed to copy all cells desired to the proper master cells. The code displays a file selection dialog box so the user may select files to process. It will process the selected files and then show the dialog box again for the user to select more files or cancel to end the process. Note that the user is not limited to one specific directory path.

    Any questions about the code, please ask. I can't answer the unasked question.

    Re: Return match from two different ranges

    Here is the formula I came up with to do what you are asking. Note that it also checks to see if a user has entered something in both column F and column G (which should be an error).


    Hope it works for you. Be sure to adjust the cell references to match your working sheet.