Text alignment in Userform Textbox

[George J]

I have a fixed height userform textbox that i would like to show the last line of.
After there is text in the textbox, enable=false.
I can see how to align for left, right and centre, but not for bottom.

I don't want to change the height or size of the textbox and just need to display the last line of data.

Can anyone assist?

 

[George J]

Got a messy workaround for this but would still be interested to know what the 'proper' procedure would be.

After the text goes into the textbox (taken from a workbook), setfocus to the textbox, then setfocus to a different textbox - then set enable = false. This leaves the textbox showing the last line of text.

Excel has the horizontal alignment, but doesn't seem to have the vertical for this.

 

[MickG]

Hi, If you fill you listbox as below or with something similar, then you could include the line to set the listbox Index as shown.

[COLOR="Navy"]Sub[/COLOR] MG01Feb20
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, ray
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
ray = Rng
[COLOR="Navy"]With[/COLOR] ListBox1
    .List = ray
    .ListIndex = .ListCount - 1
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[George J]

Thanks for that Mick

Not sure if the listbox is what i'm lloking for but i'll have a look at it.

I have a userform with a textbox on it.
Data is pulled from one cell in the textbox but this may be 3 numbers, seperated by a paragraph return.

45
54
200

I want the textbox to display the 200.
Looks like my fix only works some of the time.

 

[MickG]

Hi, If I enter a set of numbers in a cell, seperated by "Alt + Enter", to obtain a vertical list in that cell, and then run some code to insert it in a Userform TextBox, If the textbox size is only big enough for one entry, then the entry that shows, is the last entry.
What do you do that's different from that???
Regards Mick

 

[GTO]

Have you tried just plunking the last number into the textbox by stripping the last linefeed (and what's before it)?

Private Sub UserForm_Initialize()
    
    If Not InStrRev(Range("A1").Value, Chr(10)) = 0 Then
        TextBox1.Value = Mid(Range("A1").Value, InStrRev(Range("A1").Value, Chr(10)) + 1)
    Else
        TextBox1.Value = Range("A1").Value
    End If
    
End Sub

Hope that helps,

Mark

 

[George J]

Thanks for the suggestion Mark

I did think about that but as the data from the textbox may go back into the spreadsheet i didn't want to alter what should be the contents of the cell.

If it looks messy they'll just need to live with it

 

[George J]

Hi Mick, sorry i didn't see your post earlier. What you have described is exactly what i want. Not sure if my locking the textbox knocks it back to the beginning, but if they view the same entry later, it is the last line that displays. If they don't like it, i'll remove the feature and they can extract all the data manually.

{Mark, i had forgot about the InStr command and was able to use it later for something else - thanks for that}

Thanks to all for their input.

 

[Norie]

George

Why are you using a textbox if all you are doing is displaying some text/numbers?

Textboxes are usually used for user input, wouldn't a label be more appropriate if all you are doing is displaying something.

 

[George J]

Busted!!

(Almost)

Initially the textbox was mean to be filled in by the user (as well as a few of the other textboxes). Instead of them going to the website and looking for the info, i wrote a macro to search the site based on the address they select or enter, then the data can be extracted with the press of a button on the userform.

I just couldn't be bothered changing all the code for that field so i made it look like a label (changed background colour).