VBA UserForm to return a value from a spreadsheet

Newport Count

Active Member
Joined
Oct 25, 2009
Messages
328
Hi,

i have created a userform in excel that once opened allows users to fill in information that gets logged in a spreadsheet

the spreadsheet holds all data that gets sent from the form as well as another few fields that are created based on the values entered from the form

eg cell C2 in the spreadsheet uses the initial letter from one field sent from the useform and the last three numbers from another field sent from the userform

is it possible, once the user has sent an entry to the form, that a pop up box can display a different cell from the spreadsheet based on the last entries?

userform entry1: Cardiff
userform entry2: 123456

example pop up box: C456

hope that makes sense

Cheers! :confused:
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
I'm not real clear on just what you want to have the MsgBox (pop-up) display.
Do you want it to display the first character ('C') from (for example TextBox1) and the last 3 characters from TextBox2 ('456')? - Or... do you want it to display the value found (in this case) in cell C456?

Also, without seeing how you're doing what you're already doing it's kind of hard to give a solid answer so here are a couple examples you may be able to tweak to suit.

1) - to display a message box with the first & the last 3 characters...
Code:
MsgBox Left(Me.TextBox1, 1) & Right(Me.TextBox2, 3)


2) - to display a message box showing the value of the range depicted by the first & the last 3 characters...
Code:
Dim RngToShow As String
RngToShow = Left(Me.TextBox1, 1) & Right(Me.TextBox2, 3)
MsgBox Range(RngToShow).Value

If neither of these help at all then I'm lost on what you're trying to achieve.
 
Upvote 0
Hi - many thanks for your response - sorry....let me clarify what i meant:

(slightly diff example)

i require a pop-up box to appear once all the data has been completed on the form and sent/populated in the spreadsheet

i require this pop-up box to display the value of a cell in the spreadsheet that gets populated once the form has been completed - so this cell is a formula that is based on other fields that are sent via the form

form textbox 1: London - sent to cell A1

Column B is a numbered list - B1=1, B2=2, B3=3, B999 = 999 etc

Column C is a formula based on Column A (first 3 letters) & B - C1 = Lon1

can "Lon1" be displayed in the pop-up box once the form has been sent?


another example:

form textbox 1: Birmingham - sent to cell A33

C33 = Bir33

Thanks again
 
Upvote 0
Oh yeah, that would be similar to the first example above but even easier.
The only question I have is how you determine which row the entry gets made in, (therefore which row to address for the MsgBox.)
Is it just the next available row in column A? - or...?

Do you already have your textboxes coded to make their entries? (If so, can we see the code?)
 
Upvote 0
Hi -thanks for getting back to me

yes each entry made on the userform gets put into the next available line in the spreadsheet - the next blank value

code as follows:

Code:
Private Sub cmdOk_Click()
 
ActiveWorkbook.Sheets("Data Entry Log").Activate
 
    Range("A8").Select
 
    Do
    If IsEmpty(ActiveCell) = False Then
        ActiveCell.Offset(1, 0).Select
    End If
 
    Loop Until IsEmpty(ActiveCell) = True

I require the value in column C to appear in the pop-up box once the data has been sent - this should always show the latest value i.e. from the data that has just been sent

Thanks!
 
Upvote 0
The code for my textbox entries is as follows: (there are other fields on the form such as radio buttons etc - but they are all in a similar format)

ActiveCell.Offset(0, 0) = txtLocation.Value

So this would tranfer eg London from the userform to cell A1 in the spreadsheet

Thanks!
 
Upvote 0
Your loop code is (right now) only finding the next available row in Col A of the Data Entry Log sheet.
If (when) you end up with countless rows of data it's not very efficient code. We can tell it where we want the data to go without looping or activating/selecting anyting. (Selecting is almost never necessary and is also very ineffecient.)
Your looping code can be replaced with something like this.
Code:
Dim DataTarget As Range
''// Define 'DataTarget' as the first blank cell in column A
With Sheets("Data Entry Log")
    Set DataTarget = .Cells(Rows.Count, "A").End(xlUp)(2)
End With

Now I'm still not clear on how far you want this code to go but here are a few ideas.

To do what I think you've asked, you could do something like this:
Code:
Private Sub cmdOk_Click()
Dim DataTarget As Range
''// Define 'DataTarget' as the first blank cell in column A
With Sheets("Data Entry Log")
    Set DataTarget = .Cells(Rows.Count, "A").End(xlUp)(2)
End With
''// Enter the value of txtLocation into Col A
With DataTarget
    .Value = txtLocation.Value
    ''// Enter the number in Col B
    .Offset(, 1).Value = DataTarget.Row
    ''/// Enter the formula result (as a value) into Col C
    .Offset(, 2).Value = Left(DataTarget, 3) & DataTarget.Row
End With
''// Show your message box
MsgBox "The value I want to see is : " & DataTarget.Offset(, 2).Value
End Sub
Note this makes the column B & C entries as a values, not formulas. That can be easily changed if need be.

Unless you want to keep those numbers & abbreviations in columns B & C for some other reason, you could get rid of them altogether with just this behind your cmdOk button.
Code:
Private Sub cmdOk_Click()
With Sheets("Data Entry Log").Cells(Rows.Count, "A").End(xlUp)(2)
    .Value = txtLocation.Value
    ''// And show your message box like so
    MsgBox "The message I want to see is : " & _
       Left(txtLocation.Value, 3) & .Row
End With
End Sub

Does one of those give you any ideas?
 
Upvote 0
Many thanks - I have used some of your code - and kept some of mine!

I've been playing around with the code a bit and finally have it working to provide a pop-up box at the end

i'm learning a lot from this site - bit by bit - my code probably is very inefficient etc but at least it works!

thanks a lot! :LOL:
 
Upvote 0
With regards to the final message box that pops up when the form is complete:

Code:
    MsgBox "Your Unique Log Number is : " & _
    ActiveCell.Offset(, 3)

is it possible to highlight in bold and in red the part after : ?

i have amended the pop up box to display a cell from the spreadsheet

Thanks!
 
Upvote 0
is it possible to highlight in bold and in red the part after : ?
As far as I know we can't format the font properties of a messagebox. (Maybe there is a way and I just haven't had enough coffee yet...)
What you can do though, is use another little userform to replace the messagebox. Then you could format everything just the way you want it.

Learning even bit by bit is good. Makes it much more fun to help out someone who's trying to understand the code. (And pickin' it apart / putting it back together slightly different is about the best way I know of to do it.) :wink:

Good on you.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top