VBA WorksheetFunction.Max

[frontypro4x]

Hi,
New to Excel VBA but have a little experience with excel.

My question I guess is does the application.worksheetfunction.max() allow to calculate the values of 10 text boxes on a user form?

I have created a user form where the user will select a chemical name which than auto populates other text boxes with the selected chemicals info i.e. Re Entry and Preharvest interval times with the VBA Vlookup function.
The problem that I'm having is that I need to be able to get the maximum number of hours for the Re Entry text boxes as there is the possibility of selecting 10 different chemicals that could be applied at the same time, to a text box that I could use to later calculate a Re Entry date and time, as well as the Pre harvest.

I have attached the project that I am working on, it is still in early stages and doesn't have all the formulas in place yet. Just trying to get all the components figured out first and then its just copy and paste and adjust the code for the areas missing it.

Thanks for any body willing to show me what I am not understanding


So I guess I can't attach any thing yet. Must need to have a minimum amount of post I guess

 

[shg]

I would just loop through the controls, but I reckon you could do this:

  dMax = WorksheetFunction.Max(CDbl(txt1.Value), CDbl(txt2.Value), CDbl(txt3.Value))

 

[frontypro4x]

I would just loop through the controls, but I reckon you could do this:

  dMax = WorksheetFunction.Max(CDbl(txt1.Value), CDbl(txt2.Value), CDbl(txt3.Value))

Hi shg, and thanks for your time

Where would I put that code.
I have tried putting it into a separate module and calling it Max ReEntryValue and tired equalling my text box name that in the combobox change event to get it to show up each time there is a new selection made, but obviously i'm not doing missing something.
I noticed that you have brackets around (txt1.Value) is there a reason for this or is it optional?

 

[frontypro4x]

This is what I have for my code:

Option Explicit
Public Rng As Range
Public MaxValue As Variant
Sub MaxReEntryValue()
Set Rng = Checklist.txtReEntryAmount1.Value & Checklist.txtReEntryAmount2.Value & Checklist.txtReEntryAmount3.Value & Checklist.txtReEntryAmount4.Value & Checklist.txtReEntryAmount5.Value & Checklist.txtReEntryAmount6.Value & Checklist.txtReEntryAmount7.Value & Checklist.txtReEntryAmount8.Value & Checklist.txtReEntryAmount9.Value & Checklist.txtReEntryAmount10.Value
MaxValue = Application.WorksheetFunction.Max(Rng)
End Sub

I have thie above code in a separate module and trying to use the MaxValue equal txtMaxReEntry text box. I have tried running the code step by step but it keeps coming up empty and on occasion through a debug warning.
Checklist is the name of my user form that I'm trying to get to work

 

[shg]

Option Explicit

Public dMax         As Double

Sub MaxReEntryValue()
  Dim i             As Long

  dMax = -1.79E+308

  With Checklist
    For i = 1 To 10
      With .Controls("txtReEntryAmount" & i)
        If dMax < CDbl(.Value) Then dMax = CDbl(.Value)
      End With
    Next i
  End With
End Sub

 

[frontypro4x]

Ok I'm not going to pretend that I completely understand your code, but I copied and pasted it it and it is at least putty in a 0 in the txtMaxReEntry text box now, hadn't got that far before
Does the dMAx = -1.79E+308 mean something specific or is there something I should be adjusting to make it work in my situation. Tried put in 0 instead and still only getting a 0 in the text box

 

[shg]

You're looking for a maximum value. In each step, you compare the current value to the current maximum, and save it if the current value is larger.

So what should you initialize the maximum value to? Answer: A very large negative number.

-1.79E308 is (close to) the largest negative number a double can store.

 

[frontypro4x]

Ah Ok thanks, that makes sense. I changed it back now.

Any idea why I would be getting a 0 returned than.

 

[frontypro4x]

Finally I got it work perfectly, your code is in a new module and than all I had to do was just to Call on the name of Sub from that module in the change event of the combo box on my user form.

Thanks a million for taking the time to help and explain it to a newbie such as myself.

 

[shg]

You're welcome.