Referencing a textbox value from a determined value to a textbox range

**gwelf** · 08-22-2017, 09:14 AM

Hi

This is my first time posting a query, but I truly am stuck on this one.

I am coding templated outlook emails from a userform.

I would like to type in a value in a textbox and then when I generate the email, I want a value from a different textbox dependent on the typed in value.

For example:
I have two Textbox's which say: 2-5 and 5-10. Next to these are another two textboxes which say £150 and £100 respectively.

I would like to have a 5th textbox that I can type a value in, say 3.5. This will then know that it falls in the 2-5 range and so when I generate the email it will input the textbox with £150 in to the template.

I hope I explained it clearly enough, but I'm not sure it is possible.

Any help would be greatly appreciated on this.

Thanks

Gareth

**kelseygueldalewis** · 08-22-2017, 11:29 AM

Are you coding the userform in outlook or excel? Can you please provide example of your current code?

**gwelf** · 08-23-2017, 08:19 AM

Apologies let me rephrase my question, as I don't think I'm doing it correctly.

I'm coding on excel vba userform.

As an example:
On worksheet:-

A1 B1 C1 D1
0-5 £100 5-10 £150

On my userform I have a textbox. I want to enter a value in the textbox and for that to link to a cell on the worksheet dependent on range.

So, if I entered 3.5 then this would fall between 0-5 (A1) and so £100 (B1) will be linked.
If I entered 7.8 then this would fall between 5-10 (C1) and so £150 (D1) will be linked.

Is there any code I can use to activate this command on my userform?

I tried to put code in but I'm not allowed to show html coding, which is what I have used for the email template.

**gwelf** · 08-23-2017, 08:38 AM

Hopefully attachment is in this thread.

The cells on 'details' worksheet are the rebar weight and rebar cost columns, that I need to reference and link. Needs to go into light load email template.

I basically want to type a tonnage in the yellow tonnage box, and for that to reference one of the three rebar boxes depending what range it falls in to.

**kelseygueldalewis** · 08-23-2017, 08:08 AM

Since I'm not sure exactly what you're looking for I used a simple if statement to get at what you want. You will have to add #0# to your e-mail template for this to work.

Option Explicit

Sub OpenTestTemplate()
    Dim temp As Outlook.MailItem
    Dim t As Double
    Dim p As Integer
    
    t = InputBox("Please Enter the Value")
    Debug.Print t
    
    If 2 < t and t < 5 Then
        p = 100
    ElseIf 5 < t and t < 10 Then
        p = 150
    End If
    Debug.Print p
    

    Set temp = Application.CreateItemFromTemplate( _
        "C:\Users\64761\AppData\Roaming\Microsoft\" _
        & "Templates\OutlookTest.oft")
        temp.Display
        temp.Body = Replace(temp.Body, "#0#", p)

    Set temp = Nothing
End Sub

**gwelf** · 08-23-2017, 08:23 AM

The problem with that code is it only refers to a fixed range. The range changes on different rows. So I need to reference a range typed in a cell. Not sure if it's possible.

**kelseygueldalewis** · 08-23-2017, 08:22 AM

Have you tried creating a table and using vlookup instead? It will be easier for your textbox entry to find the value that way

0 100
1 100
2 100
3 100
4 100
5 100
6 150
7 150
8 150
9 150
10 150

Then use p = VLOOKUP(t, range, 2, false)

**gwelf** · 08-23-2017, 08:27 AM

The prices also change from row to row.

so it may be like:
A B C D E F
0-5 £100 5-10 £150
2-5 £75 5-8 £100
0-5 £50 5-10 £100 10-15 £150

**kelseygueldalewis** · 08-23-2017, 08:24 AM

Can you upload the file?

**kelseygueldalewis** · 08-23-2017, 08:28 AM

What is the identifier for the row you will use as your price reference?

Referencing a textbox value from a determined value to a textbox range

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Referencing a textbox value from a determined value to a textbox range

Re: Referencing a textbox value from a determined value to a textbox range

Re: Referencing a textbox value from a determined value to a textbox range

Re: Referencing a textbox value from a determined value to a textbox range

Re: Referencing a textbox value from a determined value to a textbox range

Re: Referencing a textbox value from a determined value to a textbox range

Re: Referencing a textbox value from a determined value to a textbox range

Re: Referencing a textbox value from a determined value to a textbox range

Re: Referencing a textbox value from a determined value to a textbox range

Re: Referencing a textbox value from a determined value to a textbox range

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Highlight textbox if date <=60 days referencing an array as textbox numbers

[SOLVED] Excel 2010 - Userform - display date from textbox in a label or textbox in 'ddd' format

Excel 2010 – VBA Userforms – Vlookup via Textbox, display result in another textbox

[SOLVED] Type in TextBox after referencing a different Textbox

[SOLVED] Excel Userform TextBox - Change partial string in textbox with command button

Userform: Pass textbox values to another textbox on button press

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions