Need help creating a pop-up window with specific cell data

[touyets]

hi there, first time I'm using this forum. Hope you can help me.

I'm basically creating a file where a userform comes up when you open the workbook, blocks the access to the actual workspace so that no manual entry can be done. What the userform allows you to do is add a new entry to the database (the userform has some text boxes where we type in stuff like location and name or age) after having found the next empty row in the enormous database (3.000 lines)

I have done this and I need help resolving a stupid issue I can't get over (some of you may laugh ).

After having clicked the OK button in the userform and added the data to the database, I would like the userform to have a pop-up message box with specified text like:

"The number assigned to this entry is:" and then I would like to add a variable with excel using the cell in column D of the same row of the new entry. So the end result would be :

The number assigned to this entry is: 01

I've being trying several things but nothing worked (not even close to be honest).

Cheers,

Simon

 

[StephenR]

What bit are you having trouble with? This will give you a message:

Msgbox "The number is " & Range("B11")

 

[Lewiy]

Try this:

MsgBox "The number assigned to this entry is: " & Sheets("Sheet1").Range("D" & i)

Where "Sheet1" is the name of the sheet where you are getting the data from and i is a variable containing the row number where you have just put the new entry.

 

[touyets]

Thanks a lot to the both of you but I seam having trouble with that i stuff...

it doesn't seem to recognise it. I've tried it with D2 just to see if it would work and it does so my trouble is the "D" & i. Did i miss something? DO I need to change the i or make a i= statement before this:

MsgBox "The number assigned to this entry is: " & Sheets("Database").Range("D" & i)

 

[Lewiy]

You need to define the value of i first.
To see if it works then first use:

i = 2

 

[touyets]

OK, thanks Lewiy.

and what should I put after i =

for it to select that precise row I am using as it will change all the time and I do not know what it will be...

 

[touyets]

I've tried this but there is something wrong with it...

i = Range("D:D").End(xlDown).Select
MsgBox "The number assigned to this entry is: " & Sheets("Sheet1").Range("D" & i)

 

[StephenR]

If you want the last entry in column D then use

i=Cells(Rows.Count,4).End(xlUp).Row

 

[Lewiy]

Try using:

i = Cells(Rows.Count, 4).End(xlUp).Row - 1

 

[touyets]

this is what I did and i don't get a mdebug error anymore but it won't give me the number I need... It just shows the text "This number is assigned to :"

and that's it...

'Message box
Range("D65536").End(xlUp).Select
i = ActiveCell.Row
MsgBox "The number assigned to this entry is: " & Sheets("Sheet1").Range("D" & i)