Posts by dtp81390

    So here is my coding need.

    I have a macro that upon double clicking a certain cell in column C a userform is generated.

    The information in the userform is based on the cell clicked.

    Here is what I want to write

    Programname= the name of the activesheet

    Typename = the value of Column B adjacent to the cell clicked

    woname= the value of the cell clicked

    length= search a table where column 1 of the table matches programname, column 2 of the table matches Typename, Column 3 of the table matches woname, and returns the value in Column 4

    Re: Pop Up on Double Click Message Box

    Ok so after doing a lot more research. I have discovered that I can assign variables to Userform Labels and use a macro to search for the appropriate values for those variables.

    On that note, I will close this topic and start a new one with the specific coding question that I have

    This is a complicated thing that I am trying to achieve, I do not have examples of it and I do not know if it can be done but here goes nothing.

    I have a list of programs on a sheet. On the event that the cell with the program name is double clicked a message box would be displayed that contains more information about the program.

    I would want the message box to have the following information formatted like below:

    Name of Program

    Length: 35 minutes

    Required Equipment: adsfasdfadsfasdf

    Optional Equipment: asdfdsadfsdfsdf

    Description: adfasdfasdfasf


    Exercise 1: adsfasdfasdf
    Exercise 2: adsfasdfdsf

    I want this to have a button that is either ok or close.

    These are the following options that I know of to accomplish this and the downsides that I see with them

    Comments: Can format the size of the comment box and bold text as desired. It is available on hover so I fear that it will be too cumbersome. Can you format these to appear on click?

    Data Validation: Use an Input Message in the data validation, cannot bold the text but it is easy to use and edit, but it is available only when selecting a cell. I want the user to have the option to view additional information which is why I want it on the double click instead of cell selection

    Message Box: Can run a macro that activates on double click of the assigned cells, cannot format the text as desired. Is it possible to enter this information on a separate sheet and use Index or Vlookup to find the information applicable to the cell selected and return that info in the message box? Would it take the formatting of the original text?

    User Form: This would be the best way to achieve the look that I am wanting, but since I have 400+ cells that would get this macro assigned to it would I have to create a user form for each or can I create a User Form that finds the information applicable to the cell clicked and enters it into the form so that I can create a single form that would work for all 400+ cells?

    Any other suggestions would be appreciated. Thanks

    So here is my code to generate a random number for a list of entries.

    The issue that I am having is that I then find the Max value of the random numbers and whichever entry has the max value gets entered onto another sheet. I have that part of the code figured out.

    I just need to prevent duplicates from being generated.

    Re: Appending Ranges

    I have attached an abbreviated version of the document in question.

    I have a dropdown data validation menu that when it gets changed the correct range of cells becomes visible. IE if range1 is selected then cells 9 thru 230 are visible and any rows higher than 230 are hidden, same for range2 where rows 9-230 are hidden as well as anything above 300.

    Currently this set to a static number of data input cells, but then I was thinking what if the user wants to add another data row. It is not simply a matter of inserting another row it is a matter of inserting anywhere from 2-7 rows of data with formulas and formatting. This is what is prompting the dynamic ranges as users add rows.

    I know what the ranges are and that is what matters, the users are not going to be touching the code for this. There is no way of knowing what the start value is for where range1 or range 2 begins, A default number of data entry rows will be in the document to begin with. I will set the starting range and then adjust it based on the number of times the user presses the insert data row button.

    The rows in between the ranges are completely blank with no formatting. It could be for range 1 start at cell B9 and count down until B??? is blank and that would be range 1, range 2 would then be end of range 1 + 1 row until the next blank.

    Not sure if this helps.

    In the attached file I have an abbreviated data set and workbook. There are references in formulas to a sheet that does not exist in this workbook, but that should not affect anything that I am looking for,

    I am just starting to write the code for this and I wanted to get some input on the best way to do this. I have multiple ranges of data that are laid out underneath each other. IE range1 is rows 9-41, range2 is 42 to 100, etc.

    Dim rng1 as Range

    Q1: I am new to using a variable as a range so how would I define the range to be Rows 9 thru 41

    Here is the code I am using for this question:

    Dim range1 as Range
    Set Range1 = Rows("9:41")

    Q2: How would I select the second to last row plus 2 rows above it (total of 3 rows)

    Q3: How would I select the last row of this range and insert copied cells above it.

    Q4: By inserting these rows into it how do I redefine the range to include the inserted rows. Range 1 would then be 9 thru 233 after the insert. This would also adjust all subsequent ranges by the same value.

    Q5: This is related but a slightly different circumstance. I have a range of cells that takes the data from every third row (Totals row) of the above listed range and places them on a separate sheet which is used for making charts with dynamic ranges. Anyway the cell references are Sheet1!C15 and the next cell down is Sheet1!C18, as i drag the formulas to subsequent cells they only increase by a value of 1, is there anyway to do this where the next one is always a set number of cells from the previous entry.

    Thanks in advance for the help.

    Ok so I have a workout tracking sheet for multiple workouts one after another on the same sheet. Meaning the tracking sheet for workout 1 goes from row 9 to row 100 and workout 2 starts on 102 and goes to 200 etc.

    There is a drop down box that has a macro that shows/hides the correct range for each workout.

    I want to be able to add days indefinitely to the end of a tracking sheet. For example if the user wanted to add a day to the workout 1 tracking log then they would click a button and it would add the appropriate text formatting and formulas as necessary.

    The macro would also adjust the ranges of the drop down box so that the range of workout 1 is modified when new days are added to the log.

    Added the example file.

    Ok so here is the situation.

    I have a variety of cells on Sheet 1 These are specific programs and the cells that hold the reference are not contiguous, that I would like to have a reference to Sheet 2 which lists all of the programs along with other information about those programs. There is a macro that runs automatically that adjusts values on Sheet 2 and sorts the data.

    The issue that i am running into is that if I use =Sheet2!Q9 in the Sheet 1 cell. When Sheet 2 gets sorted the cell reference in sheet 1 remains the same and is now pointing to the wrong value.

    Is there anyway to always associate the Sheet 1 cell with Sheet 2 cell with sorting. Is there a workaround for this, I am willing to use VBA to resolve this.

    I have a situation where I have 2 questions that I want to ask users of my spreadsheet, the answer is yes or no. I have provided a data validation list box for selection of Yes or No. The default answer for these questions is no.

    What I am looking for is a range of Cells G28:I33 to be grayed out and locked to user input when question 1 is No and have a data validation list box and be unlocked to user input if Question 1 is Yes.

    The same would apply to Question 2 but I assume the formatting would be the same.

    I have attached an example file with desired outcomes and the data validation. In the desired outcome example, I would like the desired outcomes to be there when the cells become active to user input.

    Any help would be appreciated

    So I have a spreadsheet that is used to track the Weight and measurements for myself in regards to my workout regimen, to track my weight loss progress.

    This is a generalization of how I have things set up and what it would look like with the gap

    [TABLE="width: 500"]














    Week 2











    Week 3









    Week 4











    Week 5










    I have defined a dynamic range for Weight with the following formula.


    I typically weigh myself every week, but occasionally I miss a week.

    When I go to chart this data I get a gap in the chart where the blank is, the chart adds the other points but does not continue the line past the blank.

    Any help in creating a dynamic range that can be charted without gaps would be appreciated.


    Re: Excel VBA Macro for Prioritizing a list


    I am finally running through testing my entire workbook and I am running accross a unique error

    On my scheduling page where I randomly schedule my workouts. The way it is run is that in one cell it schedules the workout in the next cell it runs a Countif function that counts how many times that specific program is schedule. The problem is that the Countif cycles through a list of numbers, I am assuming doing a Countif of already scheduled programs and it is hitting the end number which causes that program to be marked as completed and no longer schedule.

    For example: I have Program 1: Basic which was already Scheduled 78 times which is the number of times until completion. When I schedule Program 1: Mastery, even though it has been scheduled 18 times (78 is the length of the program), the program gets checked as completed. How can I remedy this issue.

    Re: Excel VBA Macro for Prioritizing a list

    So I am not sure what the issue is. I have Excel 2013 on one computer and excel 2010 on the other and I have been working on both computers on this workbook.

    The error i am getting is Run-time error 1004 Unable to get the RandBetween property of the WorksheetFunction class

    I run the following script that you helped me with on here to get the random value for scheduling. The macro executes and provides the random value. It does change the value but I get an error message and it asks me to end or debug. Not sure why. I have not changed the cells or columns or anything like that.

    Sub rndScore()    Dim Lrow As Integer
        Dim Srow As Integer
        Lrow = Sheets("Programs").Cells(Rows.count, 19).End(xlUp).Row
        For Srow = 9 To Lrow
            Sheets("Programs").Cells(Srow, 26).Value = WorksheetFunction.RandBetween(10, Sheets("Programs").Cells(Srow, 19) * 100000000000#) / 1000000000
        Next Srow
    End Sub

    Re: Excel VBA Macro for Prioritizing a list

    Hey so I was able to debug the issues as well as get the scheduling to all work the way I want it to. Thanks again for all your help.

    As I was developing all this I came across something that I want to be user input so here is the breakdown of what I would need help.

    I have a PREFERENCES button on my Programs sheet that when clicked I want it to bring up a UserForm that has toggle buttons on it. This user form will drive the values of constants that are used in the priority calculation. (Currently in the sub for wType I have 5 Constants: Martial Arts, Strength, Mixed, Cardio, Dance with their respective point values which are based on my preferences. I want this to be user driven)

    The first question is What is your favorite Type of Program? and will have 5 toggles 1 for each of the types listed above. When the toggle is true then it needs to drive the variable that determines the point value

    The second question is What is your second favorite Type of Program? and will have 4 toggles 1 for each of the type that was not selected for the first question and derives the same variable just with a different point value

    The same for the subsequent questions until there are no more.

    Re: Excel VBA Macro for Prioritizing a list

    In the updated workbook it goes to AA i believe on the priorities sheet.

    What ways can you get the file. I can send you a dropbox link or email the file to you if you need it. Thanks for the help

    Also just a note on your code from your last post. It looks good but the last part with assigning the day is where I think you might not have understood what I was looking for. Below is a sample week of what I would be looking for

    Day 1 P90X: Day 1
    Day 2 Insanity: Day 1
    Day 3 P90X: Day 2
    Day 4 Combat: Day 1
    Day 5 P90X: Day 3
    Day 6 Insanity: Day 2
    Day 7 Rest

    What I got from your code is that it would fill out the left column, and not concatenate the workout with the day number attached like in the second column.

    Not sure if you would be able to do this.

    Let me know how you want to receive the file.