Posts by dtp81390

Re: Userform lookup screen

How would you apply the information in the userform to a label not to a text box.

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

Exercises:

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.

Sub random()


    Dim Lrow As Integer
    Dim Srow As Integer
    Lrow = Sheets("Priorities").Cells(Rows.Count, 19).End(xlUp).Row
     
    For Srow = 9 To Lrow
        Sheets("Priorities").Cells(Srow, 26).Value = Application.WorksheetFunction.RandBetween(1, 100)
              
    Next Srow
    
End Sub

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

Quote from arthurbr;770659

Cross posted at http://www.excelguru.ca/forums…-Select-last-row-of-range

This post has been closed on the other forum. This is the only place where it is active

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.

Re: Insert new rows with formatting.

I posted from my phone, when I get to a comp I can add links

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"]

Start

1/1/2016

300

Week 2

1/8/2016

295

Week 3

1/15/2016

Week 4

1/22/2016

290

Week 5

1/29/2016

292

[/TABLE]

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

=OFFSET(Measurements!$C$10,0,0,COUNTA(Measurements!$C$9:$C784))

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.

Thanks

Re: Excel VBA Macro for Prioritizing a list

So,

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.

Private Sub cmdSchedule_Click()
'    Dim Count As Integer
'    Count = 0
    
'    Do Until Count = 7
    
        Call rndScore




        Call getMax
          
        Dim Drow As Integer
        Dim Prow As Integer
        Prow = Sheets("Schedule").Cells(Rows.Count, 4).End(xlUp).Row
        Drow = Prow + 1
    
       
        
        
        If (Drow - 8) Mod 28 = 0 Then
            Sheets("Schedule").Activate
            Sheets("Schedule").Cells(Drow, 4).Value = "Fit Test"
        Else
            If (Drow - 1) Mod 7 <> 0 Then
                Call getProgram
                Call getDay
            Else
                Sheets("Schedule").Activate
                Sheets("Schedule").Cells(Drow, 4).Value = "Rest Day"
            End If
        End If
      
        Call detailSchedule
'    Count = Count + 1
'    Loop
    
End Sub




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 = Application.WorksheetFunction.RandBetween(10, Sheets("Programs").Cells(Srow, 19).Value * 1000000000000#) / 10000000000#
  
    Next Srow
    
End Sub








Sub getMax()




    Sheets("Programs").Activate




    ActiveSheet.Cells(9, 27).Value = "=Max(z:z)"




End Sub








Sub getProgram()
    
    Dim Lrow As Integer
    Dim Srow As Integer
    Dim Drow As Integer
    Dim Prow As Integer
    Prow = Sheets("Schedule").Cells(Rows.Count, 4).End(xlUp).Row
    Drow = Prow + 1
    Lrow = ActiveSheet.Cells(Rows.Count, 26).End(xlUp).Row
    
    For Srow = 9 To Lrow
        If ActiveSheet.Cells(Srow, 26).Value = ActiveSheet.Cells(9, 27).Value Then
            Sheets("Schedule").Cells(Drow, 4).Value = Sheets("Programs").Cells(Srow, 3).Value
        End If
    Next Srow
    
    Sheets("Schedule").Activate
    
End Sub




Sub getDay()




    Dim Srow As Integer
    Dim Prow As Integer
    Dim Lrow As Integer
    Dim wCount As Integer
    
    Lrow = Sheets("Programs").Cells(Rows.Count, 3).End(xlUp).Row
    Prow = Sheets("Schedule").Cells(Rows.Count, 4).End(xlUp).Row
    
    For Srow = 9 To Prow
         
        wCount = WorksheetFunction.CountIf(Range("D:D"), Cells(Srow, 4))
        Sheets("Schedule").Cells(Prow, 9).Value = "Day"
        Sheets("Schedule").Cells(Prow, 10).Value = wCount
          
        For i = 9 To Lrow
            If Sheets("Schedule").Cells(Prow, 4).Value = Sheets("Programs").Cells(i, 3).Value And wCount = Sheets("Programs").Cells(i, 7).Value Then
                Sheets("Programs").Cells(i, 17).Value = "Yes"
            End If
        Next i
                   
    Next Srow
    Sheets("Programs").Activate
    Sheet1.cmdPriority_Click
    Sheets("Schedule").Activate
    
End Sub




Private Sub cmdClear_Click()
    Dim answer As Integer
    
    answer = MsgBox("Are you sure you want to clear all cells?", vbYesNo + vbQuestion, "Clear Contents")
    
    If answer = vbYes Then
        Range("D9:J10000").ClearContents




    Else
        'Do Nothing
    End If




End Sub




Sub detailSchedule()
    Dim Lrow As Integer
    Dim Prow As Integer
    Dim Srow As Integer
    Dim Drow As Integer
    
    Lrow = Sheets("Schedule").Cells(Rows.Count, 4).End(xlUp).Row
    Prow = Sheets("Detailed Schedule").Cells(Rows.Count, 4).End(xlUp).Row
    Drow = Sheets("Detailed Schedule").Cells(Rows.Count, 26).End(xlUp).Row
        
    For Srow = 9 To Lrow
        Sheets("Detailed Schedule").Cells(Srow, 4).Value = Sheets("Schedule").Cells(Srow, 4).Value & ": " & Sheets("Schedule").Cells(Srow, 9).Value & Str(Sheets("Schedule").Cells(Srow, 10))
        For i = 9 To Drow
            If (Srow - 8) Mod 28 = 0 Then
                Sheets("Detailed Schedule").Cells(Srow, 4).Value = "Fit Test"
            Else
                If (Srow - 1) Mod 7 = 0 Then
                    Sheets("Detailed Schedule").Cells(Srow, 4).Value = "Rest Day"
                Else
                    If Sheets("Detailed Schedule").Cells(Srow, 4).Value = Sheets("Detailed Schedule").Cells(i, 26).Value Then Sheets("Detailed Schedule").Cells(Srow, 8).Value = Sheets("Detailed Schedule").Cells(i, 27).Value
                    If Sheets("Detailed Schedule").Cells(Srow, 4).Value = Sheets("Detailed Schedule").Cells(i, 26).Value Then Sheets("Detailed Schedule").Cells(Srow, 14).Value = Sheets("Detailed Schedule").Cells(i, 28).Value
                    If Sheets("Detailed Schedule").Cells(Srow, 4).Value = Sheets("Detailed Schedule").Cells(i, 26).Value Then Sheets("Detailed Schedule").Cells(Srow, 15).Value = Sheets("Detailed Schedule").Cells(i, 29).Value
                End If
            End If
        Next i
    Next Srow
    
End Sub

Re: Excel VBA Macro for Prioritizing a list

That worked thanks

Re: Excel VBA Macro for Prioritizing a list

Is there anyway we can make sure that all numbers are above this. There were a few more workouts added which made more priorities. if we divide by 10 less then would it work?

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.

Re: Excel VBA Macro for Prioritizing a list

it is too large to post on here. I tried several times