Userform textbox wordwrap/dare format rowsource

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,302
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi all


i have a problem on a few things


1) I have a text box where a value is populated automatically based on the colour selected. How do I have this textbox so that there is no cursor there and I can't select it and it's for read only?


2) I populate a cbo box with months from a sheet but the data show as numerical value and not dare format
ie cbodata.rowsource = "sheet1!h2:h20"


3) I have a text box to input notes rectangles shape
when input text, the text goes forever
how can I word wrap it so that when it goes to end of the size of textbox, it moves to next line
the word wrap is on so not sure why this ain't working

4) i also have 2 userforms. If i press add colour then this 2nd userform pops up with a colour picker. Once i select the colour, i change a lablel background in my userform1 and then i close this 2nd userform.
Now what i want to do is also have this userform2 (colour picker) pop up when i double click a cell and then once selected, change the cell called from/active cell colour to the colour picked. So this one will not refer to userform1.

so what i need is to have an if statement and see who called this userform 2
was it from a button on userform 1 or a cell on spreadsheet
if userform 1 then change label on userform1 colour to what i selected
or
if called by cell double click then change the active cell colour to what i selected

please help me
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
A few answers:

1. Use a Label rather than a TextBox.

2. Add this code:

Code:
Private Sub cbodata_Change()
    cbodata.Value = Format(cbodata.Value, "dd/mm/yyyy")
End Sub

3. Set the TextBox's MultiLine property to True.

4. Please post your VBA code for UserForm2.
 
Upvote 0
HI Andrew
this is the code

Can this be shortened also

Code:
Private Sub lblRed_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    UserForm1.lblColour.BackColor = RGB(192, 0, 0)
    UserForm1.txtPercentage.Text = Sheets("Sheet1").Range("D2").Text
    UserForm2.Hide
End Sub
Private Sub lblBlue_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    UserForm1.lblColour.BackColor = RGB(0, 176, 240)
    UserForm1.txtPercentage.Text = Sheets("Sheet1").Range("D3").Text
    UserForm2.Hide
End Sub
Private Sub lblGreen_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    UserForm1.lblColour.BackColor = RGB(0, 176, 80)
    UserForm1.txtPercentage.Text = Sheets("Sheet1").Range("D4").Text
    UserForm2.Hide
End Sub
Private Sub lblYellow_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    UserForm1.lblColour.BackColor = RGB(255, 255, 0)
    UserForm1.txtPercentage.Text = Sheets("Sheet1").Range("D5").Text
    UserForm2.Hide
End Sub
Private Sub lblOrange_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    UserForm1.lblColour.BackColor = RGB(226, 107, 10)
    UserForm1.txtPercentage.Text = Sheets("Sheet1").Range("D6").Text
    UserForm2.Hide
End Sub


Private Sub UserForm_Initialize()
    Me.lblRed.BackColor = RGB(192, 0, 0)
    Me.lblBlue.BackColor = RGB(0, 176, 240)
    Me.lblGreen.BackColor = RGB(0, 176, 80)
    Me.lblYellow.BackColor = RGB(255, 255, 0)
    Me.lblOrange.BackColor = RGB(226, 107, 10)
    
   Me.txtRed.Value = Sheets("Sheet1").Range("D2").Text
   Me.txtBlue.Value = Sheets("Sheet1").Range("D3").Text
   Me.txtGreen.Value = Sheets("Sheet1").Range("D4").Text
   Me.txtYellow.Value = Sheets("Sheet1").Range("D5").Text
   Me.txtOrange.Value = Sheets("Sheet1").Range("D6").Text
    
End Sub
 
Upvote 0
Ps what is the default RGB color background for a label?
So if i press clear form, i will make every thing clear and the label back its default colour (lblColor)

Thank YOu again
 
Upvote 0
Which cell(s) do you want to colour by double-clicking? Wouldn't it be easier to use the Fill control on the Ribbon's Home tab?
 
Upvote 0
Hi Andrew

firstly id like to say thank you

yes, you would have thought its easier to use the colour picker from home ribbob but my manager wanted to restrict the colour picker the the colours i have done on this userform2 (colour picker)

the range for double click will be for range (d2:O and last row)
the last row is dependant on the last row of column a in that sheet. This will also be set up exactly the same 3 sheets identical.

ps 1) what is the default colour (rgb) for the label so when i press clear it clears form to default

2) is the code done ok or could it have been condensed? And can i unload userform 2 when i choose i colour or do i need to hide it as i have done in the code

3) when would the hide method for userforms be used (just generally) so i know when to use this

many thanks
 
Upvote 0
Hi Andrew

was that enough information or do you need more

thank you love
 
Upvote 0
Try this:

Code:
'General Module

Public Cell As Range

'Worksheet Module

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim LR As Long
    Dim Rng As Range
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Set Rng = Range("D2:O" & LR)
    If Not Application.Intersect(Target, Rng) Is Nothing Then
        Set Cell = Target
        UserForm2.Show
        Cancel = True
    End If
End Sub

'UserForm2 Module

Private Sub lblRed_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    If Not Cell Is Nothing Then
        Cell.Interior.Color = RGB(192, 0, 0)
        Set Cell = Nothing
        Unload Me
    Else
        UserForm1.lblColour.BackColor = RGB(192, 0, 0)
        UserForm1.txtPercentage.Text = Sheets("Sheet1").Range("D2").Text
        UserForm2.Hide
    End If
End Sub

Private Sub lblBlue_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    If Not Cell Is Nothing Then
        Cell.Interior.Color = RGB(0, 176, 240)
        Set Cell = Nothing
        Unload Me
    Else
        UserForm1.lblColour.BackColor = RGB(0, 176, 240)
        UserForm1.txtPercentage.Text = Sheets("Sheet1").Range("D3").Text
        UserForm2.Hide
    End If
End Sub

Private Sub lblGreen_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    If Not Cell Is Nothing Then
        Cell.Interior.Color = RGB(0, 176, 80)
        Set Cell = Nothing
        Unload Me
    Else
        UserForm1.lblColour.BackColor = RGB(0, 176, 80)
        UserForm1.txtPercentage.Text = Sheets("Sheet1").Range("D4").Text
        UserForm2.Hide
    End If
End Sub

Private Sub lblYellow_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    If Not Cell Is Nothing Then
        Cell.Interior.Color = RGB(255, 255, 0)
        Set Cell = Nothing
        Unload Me
    Else
        UserForm1.lblColour.BackColor = RGB(255, 255, 0)
        UserForm1.txtPercentage.Text = Sheets("Sheet1").Range("D5").Text
        UserForm2.Hide
    End If
End Sub

Private Sub lblOrange_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    If Not Cell Is Nothing Then
        Cell.Interior.Color = RGB(226, 107, 10)
        Set Cell = Nothing
        Unload Me
    Else
        UserForm1.lblColour.BackColor = RGB(226, 107, 10)
        UserForm1.txtPercentage.Text = Sheets("Sheet1").Range("D6").Text
        UserForm2.Hide
    End If
End Sub

To reset the Label's BackColor:

Code:
lblColour.BackColor = &H8000000F

You would Hide a UserForm if you wanted to use its settings in subsequent code. Otherwise you would Unload it.

The code is fine as it is. In what way do you think that it could be condensed?
 
Upvote 0
Thank you

couple of questions

could i have used the unload of me instead the hide method in my double click code?

what does the cancel do in the worksheet double code event?
is the default set to true?

I thought just encase you guys are the experts so you probably wuda had a more consensed code but it was more to get more better wuth coding

if you think this is best then i know im on the right track
 
Upvote 0
As I said you would use Unload if you don't want to use the UserForm's settings in subsequent code (outside the UserForm's module). Hide leaves the UserForm and its settings in memory. Unload removes it from memory.

Cancel cancels what would normally occur when a cell is double-clicked, ie Edit mode if 'Allow editing directly in cells' is set.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

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