Userform textbox wordwrap/dare format rowsource

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,188
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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,188
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,188
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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?
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,188
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

mahmed1

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

ADVERTISEMENT

Hi Andrew

was that enough information or do you need more

thank you love
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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?
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,188
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,461
Messages
5,528,922
Members
409,847
Latest member
Foster034
Top