Textbox Default Value Not displaying During Userform Initialization; Selection Highlighting

A

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,570
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
With the code below, I am trying to highlight a default value of 000000 in a textbox (textbox1) in my userform. This will allow the user to overwrite the default entry without having to first select it.

Code: 
        With TextBox1               'rental number
            'cb_mri.Visible = False
            .Locked = False
            .BackColor = RGB(255, 255, 255)
            .Value = format(0, "000000") 'new rental
            .SetFocus
            .SelStart = 0
            .SelLength = Len(.Text)
        End With

The default value of "000000" isn't showing in the textbox let along that value being highlighted.

Is anyone able to tell me where the problem exists?
 
Sort by date Sort by votes
Do you have any other code in the initialise event that might conflict?
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Perhaps, but I've been through this code so much now that things are becoming just a blur. It appears I repeat the process at the end of my initialise code, but I don't think it contradicts what was already done. If anything it's just redundant. If you don't mind?

Code: 
Public Sub UserForm_Initialize()
    'Stop
    Dim l_mr As Long
    Dim temp_ws As Worksheet
    Dim CH As Integer
    Dim df1 As Integer
    Dim test_mr
    Dim ai_typelist As String
    Dim lrow_a, lrow_p As Integer
    Dim Cl As Range
    
    'Set temp_ws = Workbooks("schedule.csv").Worksheets("temp_ws")
    
    mbevents = False
    
    CH = 0 'reset customer information change holder (0=no change, 1=change)
    
    If Application.WorksheetFunction.Count(ws_vh.Range("L:M")) > 0 Then 'mri=true
        TextBox1.Visible = False
        MsgBox "Combobox enabled."
        mri = True 'missing rental flag
'        Stop
    Else
        With TextBox1               'rental number
            cb_mri.Visible = False
            .Locked = False
            .BackColor = RGB(255, 255, 255)
            '.Value = format(0, "000000") 'new rental
            '.SetFocus
            '.SelStart = 0
            '.SelLength = Len(.Text)
        End With
    End If
    
    If l_mr <> 1 Then uf2_eliminate.Visible = False 'NO MISSING RECORDS
    If df1 < 4 Then uf2_eliminate.Visible = True
    
    Label34.Caption = "   Please enter valid permit number."
    proceed1.Enabled = True
    cmdb_agrmnt.Enabled = False
    submit1.Enabled = False
    delete1.Enabled = False
    edit1.Enabled = False
    amm_no.Value = 0
    amm_no.Locked = True
    date1.Value = format(Date, "dd-mmm")
    date1.Locked = True
    ai_type.Value = ""
    ai_type.List = Workbooks("Rental_Detail.xlsm").Names("ai_typelist").RefersToRange.Value
    'ai_type.List = = Workbooks("Rental_Detail")
    ai_type.BackColor = RGB(0, 126, 167) 'celadon blue
    ai_function.BackColor = RGB(255, 255, 255)
    ai_function.Value = ""
    ai_function.Enabled = False
    ai_league.Value = ""
    ai_league.Enabled = False
    ai_calibre.Value = ""
    ai_calibre.Enabled = False
    ai_division.Value = ""
    ai_division.Enabled = False
    ai_event.Value = ""
    ai_event.Enabled = False
    'baseball
    ai_basedist.Value = ""
    ai_basedist.Locked = True
    ai_pitchdist.Value = ""
    ai_pitchdist.Locked = True
    ai_bbox.Value = ""
    ai_bbox.Locked = True
    ai_safety.Value = ""
    ai_safety.Locked = True
    ai_circle.Value = ""
    ai_circle.Locked = True
    ai_mat.Value = ""
    ai_mat.Locked = True
    ai_safeline.Value = ""
    ai_safeline.Locked = True
    ai_commit.Value = ""
    ai_commit.Locked = True
    ai_runline.Value = ""
    ai_runline.Locked = True
    ai_other1.Value = ""
    ai_other1.Locked = True
    ai_other2.Value = ""
    ai_other2.Locked = True
    ai_comment = ""
    'courts
    ai_setup.Value = ""
    ai_setup.Locked = True
    ai_other3.Value = ""
    ai_other3.Locked = True
    ai_other4.Value = ""
    ai_other4.Locked = True
    'fields
    ai_layout.Value = ""
    ai_layout.Locked = True
    ai_goals.Value = ""
    ai_goals.Locked = True
    ai_other5.Value = ""
    ai_other5.Locked = True
    ai_other6.Value = ""
    ai_other6.Locked = True
    'greenspace
    ai_water.Value = False
    ai_water.Locked = True
    ai_hydro.Value = False
    ai_hydro.Locked = True
    ai_attendance = 0
    ai_tables = 0
    ci_league.Locked = True
    ci_affiliated.Value = "N"
    ci_affiliated.Locked = True
    ci_affiliated.BackColor = RGB(255, 255, 255)
    'primary name
    ci_name1.Value = ""
    ci_name1.Locked = True
    ci_name1.BackColor = RGB(255, 255, 255)
    ci_email1.Value = ""
    ci_email1.Locked = True
    ci_email1.BackColor = RGB(255, 255, 255)
    ci_tele1a.Value = format(0, "000.000.0000")
    ci_tele1a.BackColor = RGB(255, 255, 255)
    ci_tele1a.Locked = True
    ci_tele1b.Value = format(0, "000.000.0000")
    ci_tele1b.BackColor = RGB(255, 255, 255)
    ci_tele1b.Locked = True
    'secondary name
    ci_name2.Value = ""
    ci_name2.Locked = True
    ci_name2.BackColor = RGB(255, 255, 255)
    ci_email2.Value = ""
    ci_email2.Locked = True
    ci_email2.BackColor = RGB(255, 255, 255)
    ci_tele2a.Value = format(0, "000.000.0000")
    ci_tele2a.BackColor = RGB(255, 255, 255)
    ci_tele2a.Locked = True
    ci_tele2b.Value = format(0, "000.000.0000")
    ci_tele2b.BackColor = RGB(255, 255, 255)
    ci_tele2b.Locked = True
    
    MultiPage1.Value = 1
    MultiPage1.Visible = False
    MultiPage2.Value = 0
    Frame8.Visible = False
        
    group_1.Height = 124.5
    'If Application.WorksheetFunction.Count(ws_vh.Range("L:M")) = 0 Then
    If mri = False Then
        With TextBox1
            '.BackColor = RGB(255, 255, 255)
            'If l_mr > 0 Then 'this has come in from module 21 reporting missing rentals prior to workorder prep
            '.Value = format(test_mr.miss_rn.Value, "######")
            .Value = format(0, "######") 'new rental
            .BackColor = RGB(0, 168, 232)
            .SetFocus
            .SelStart = 0
            .SelLength = Len(.Text)
        End With
     Else 'combobox
        With CreateObject("scripting.dictionary")
            For Each Cl In ws_vh.Columns("L:M").SpecialCells(xlConstants, xlNumbers)
                If Cl <> "" Then .Item(Cl.Value) = Empty
            Next Cl
            Me.cb_mri.List = .Keys
            Me.cb_mri.ListIndex = 0
            If .Count = 1 Then Me.cb_mri.Locked = True
        End With
        cb_mri.BackColor = RGB(0, 168, 232)
        Label34.Caption = "   Select missing rental."
     End If
        
    'End With
    mbevents = True
End Sub
 
Last edited:
Upvote 0
If this
Code: 
Application.WorksheetFunction.Count(ws_vh.Range("L:M"))
results in 0 then both of the textbox1 sections will trigger, so that you end-up with a blue textbox with nothing in it.
 
Upvote 0
Hmmm ...
I changed that to something a bit more reliable . Changes were made as highlighted in purple.
In my testing, mri = false and the code in purple is executed both times.

Rich (BB code): 
Public Sub UserForm_Initialize()
    'Stop
    Dim l_mr As Long
    Dim temp_ws As Worksheet
    Dim CH As Integer
    Dim df1 As Integer
    Dim test_mr
    Dim ai_typelist As String
    Dim lrow_a, lrow_p As Integer
    Dim Cl As Range
    
    'Set temp_ws = Workbooks("schedule.csv").Worksheets("temp_ws")
    
    mbevents = False
    
    CH = 0 'reset customer information change holder (0=no change, 1=change)
    
    'If Application.WorksheetFunction.Count(ws_vh.Range("L:M")) > 0 Then 'mri=true
    If mri = True Then
        TextBox1.Visible = False
        MsgBox "Combobox enabled."
        mri = True 'missing rental flag
'        Stop
    Else
        With TextBox1               'rental number
            cb_mri.Visible = False
            .Locked = False
            .BackColor = RGB(255, 255, 255)
            .Value = format(0, "000000") 'new rental
            .SetFocus
            .SelStart = 0
            .SelLength = Len(.Text)
        End With
    End If
    
    If l_mr <> 1 Then uf2_eliminate.Visible = False 'NO MISSING RECORDS
    If df1 < 4 Then uf2_eliminate.Visible = True
    
    Label34.Caption = "   Please enter valid permit number."
    proceed1.Enabled = True
    cmdb_agrmnt.Enabled = False
    submit1.Enabled = False
    delete1.Enabled = False
    edit1.Enabled = False
    amm_no.Value = 0
    amm_no.Locked = True
    date1.Value = format(Date, "dd-mmm")
    date1.Locked = True
    ai_type.Value = ""
    ai_type.List = Workbooks("Rental_Detail.xlsm").Names("ai_typelist").RefersToRange.Value
    'ai_type.List = = Workbooks("Rental_Detail")
    ai_type.BackColor = RGB(0, 126, 167) 'celadon blue
    ai_function.BackColor = RGB(255, 255, 255)
    ai_function.Value = ""
    ai_function.Enabled = False
    ai_league.Value = ""
    ai_league.Enabled = False
    ai_calibre.Value = ""
    ai_calibre.Enabled = False
    ai_division.Value = ""
    ai_division.Enabled = False
    ai_event.Value = ""
    ai_event.Enabled = False
    'baseball
    ai_basedist.Value = ""
    ai_basedist.Locked = True
    ai_pitchdist.Value = ""
    ai_pitchdist.Locked = True
    ai_bbox.Value = ""
    ai_bbox.Locked = True
    ai_safety.Value = ""
    ai_safety.Locked = True
    ai_circle.Value = ""
    ai_circle.Locked = True
    ai_mat.Value = ""
    ai_mat.Locked = True
    ai_safeline.Value = ""
    ai_safeline.Locked = True
    ai_commit.Value = ""
    ai_commit.Locked = True
    ai_runline.Value = ""
    ai_runline.Locked = True
    ai_other1.Value = ""
    ai_other1.Locked = True
    ai_other2.Value = ""
    ai_other2.Locked = True
    ai_comment = ""
    'courts
    ai_setup.Value = ""
    ai_setup.Locked = True
    ai_other3.Value = ""
    ai_other3.Locked = True
    ai_other4.Value = ""
    ai_other4.Locked = True
    'fields
    ai_layout.Value = ""
    ai_layout.Locked = True
    ai_goals.Value = ""
    ai_goals.Locked = True
    ai_other5.Value = ""
    ai_other5.Locked = True
    ai_other6.Value = ""
    ai_other6.Locked = True
    'greenspace
    ai_water.Value = False
    ai_water.Locked = True
    ai_hydro.Value = False
    ai_hydro.Locked = True
    ai_attendance = 0
    ai_tables = 0
    ci_league.Locked = True
    ci_affiliated.Value = "N"
    ci_affiliated.Locked = True
    ci_affiliated.BackColor = RGB(255, 255, 255)
    'primary name
    ci_name1.Value = ""
    ci_name1.Locked = True
    ci_name1.BackColor = RGB(255, 255, 255)
    ci_email1.Value = ""
    ci_email1.Locked = True
    ci_email1.BackColor = RGB(255, 255, 255)
    ci_tele1a.Value = format(0, "000.000.0000")
    ci_tele1a.BackColor = RGB(255, 255, 255)
    ci_tele1a.Locked = True
    ci_tele1b.Value = format(0, "000.000.0000")
    ci_tele1b.BackColor = RGB(255, 255, 255)
    ci_tele1b.Locked = True
    'secondary name
    ci_name2.Value = ""
    ci_name2.Locked = True
    ci_name2.BackColor = RGB(255, 255, 255)
    ci_email2.Value = ""
    ci_email2.Locked = True
    ci_email2.BackColor = RGB(255, 255, 255)
    ci_tele2a.Value = format(0, "000.000.0000")
    ci_tele2a.BackColor = RGB(255, 255, 255)
    ci_tele2a.Locked = True
    ci_tele2b.Value = format(0, "000.000.0000")
    ci_tele2b.BackColor = RGB(255, 255, 255)
    ci_tele2b.Locked = True
    
    MultiPage1.Value = 1
    MultiPage1.Visible = False
    MultiPage2.Value = 0
    Frame8.Visible = False
        
    group_1.Height = 124.5
    'If Application.WorksheetFunction.Count(ws_vh.Range("L:M")) = 0 Then
    If mri = False Then
        With TextBox1
            '.BackColor = RGB(255, 255, 255)
            'If l_mr > 0 Then 'this has come in from module 21 reporting missing rentals prior to workorder prep
            '.Value = format(test_mr.miss_rn.Value, "######")
            .Value = format(0, "######") 'new rental
            .BackColor = RGB(0, 168, 232)
            .SetFocus
            .SelStart = 0
            .SelLength = Len(.Text)
        End With
     Else 'combobox
        With CreateObject("scripting.dictionary")
            For Each Cl In ws_vh.Columns("L:M").SpecialCells(xlConstants, xlNumbers)
                If Cl <> "" Then .Item(Cl.Value) = Empty
            Next Cl
            Me.cb_mri.List = .Keys
            Me.cb_mri.ListIndex = 0
            If .Count = 1 Then Me.cb_mri.Locked = True
        End With
        cb_mri.BackColor = RGB(0, 168, 232)
        Label34.Caption = "   Select missing rental."
        mri = False
     End If
        
    'End With
    mbevents = True
End Sub
 
Upvote 0
As far as textbox1 is concerned, what do you want to happen if this line
Code: 
Application.WorksheetFunction.Count(ws_vh.Range("L:M"))
is 0 & what should happen if it's >0
 
Upvote 0
Hi Fluff,

I got rid of that line, replacing it with
Code: 
If mri = True Then

If mri = false, then textbox1 will unlock, the background color will change to something it's value will equal zero and be displayed in the box as 000000 and be configured to allow the user to simply type in a new value of the default 000000. (the selection highlighting.)

If mri = true, textbox1 is out of the question and is replaced by a combobox allowing the user to select specific values.

Some considerations that come to mind:
This form is form #2 that is open. both forms showmodal properties are false.
The combobox referred to is in the exact same place as textbox1. Their individual visible properties determine which one is accessible.
 
Upvote 0
In that case how about
Code: 
Public Sub UserForm_Initialize()
    'Stop
    Dim l_mr As Long
    Dim temp_ws As Worksheet
    Dim CH As Integer
    Dim df1 As Integer
    Dim test_mr
    Dim ai_typelist As String
    Dim lrow_a, lrow_p As Integer
    Dim Cl As Range
    
    'Set temp_ws = Workbooks("schedule.csv").Worksheets("temp_ws")
    
    mbevents = False
    
    CH = 0 'reset customer information change holder (0=no change, 1=change)
    
    If Application.WorksheetFunction.Count(ws_vh.Range("L:M")) > 0 Then 'mri=true
        TextBox1.Visible = False
        MsgBox "Combobox enabled."
        mri = True 'missing rental flag
'        Stop
    Else
        With TextBox1               'rental number
            cb_mri.Visible = False
            .Locked = False
            .BackColor = RGB(255, 255, 255)
            .Value = Format(0, "000000") 'new rental
            .SetFocus
            .SelStart = 0
            .SelLength = Len(.Text)
        End With
    End If
    
    If l_mr <> 1 Then uf2_eliminate.Visible = False 'NO MISSING RECORDS
    If df1 < 4 Then uf2_eliminate.Visible = True
    
    Label34.Caption = "   Please enter valid permit number."
    proceed1.Enabled = True
    cmdb_agrmnt.Enabled = False
    submit1.Enabled = False
    delete1.Enabled = False
    edit1.Enabled = False
    amm_no.Value = 0
    amm_no.Locked = True
    date1.Value = Format(Date, "dd-mmm")
    date1.Locked = True
    ai_type.Value = ""
    ai_type.List = Workbooks("Rental_Detail.xlsm").Names("ai_typelist").RefersToRange.Value
    'ai_type.List = = Workbooks("Rental_Detail")
    ai_type.BackColor = RGB(0, 126, 167) 'celadon blue
    ai_function.BackColor = RGB(255, 255, 255)
    ai_function.Value = ""
    ai_function.Enabled = False
    ai_league.Value = ""
    ai_league.Enabled = False
    ai_calibre.Value = ""
    ai_calibre.Enabled = False
    ai_division.Value = ""
    ai_division.Enabled = False
    ai_event.Value = ""
    ai_event.Enabled = False
    'baseball
    ai_basedist.Value = ""
    ai_basedist.Locked = True
    ai_pitchdist.Value = ""
    ai_pitchdist.Locked = True
    ai_bbox.Value = ""
    ai_bbox.Locked = True
    ai_safety.Value = ""
    ai_safety.Locked = True
    ai_circle.Value = ""
    ai_circle.Locked = True
    ai_mat.Value = ""
    ai_mat.Locked = True
    ai_safeline.Value = ""
    ai_safeline.Locked = True
    ai_commit.Value = ""
    ai_commit.Locked = True
    ai_runline.Value = ""
    ai_runline.Locked = True
    ai_other1.Value = ""
    ai_other1.Locked = True
    ai_other2.Value = ""
    ai_other2.Locked = True
    ai_comment = ""
    'courts
    ai_setup.Value = ""
    ai_setup.Locked = True
    ai_other3.Value = ""
    ai_other3.Locked = True
    ai_other4.Value = ""
    ai_other4.Locked = True
    'fields
    ai_layout.Value = ""
    ai_layout.Locked = True
    ai_goals.Value = ""
    ai_goals.Locked = True
    ai_other5.Value = ""
    ai_other5.Locked = True
    ai_other6.Value = ""
    ai_other6.Locked = True
    'greenspace
    ai_water.Value = False
    ai_water.Locked = True
    ai_hydro.Value = False
    ai_hydro.Locked = True
    ai_attendance = 0
    ai_tables = 0
    ci_league.Locked = True
    ci_affiliated.Value = "N"
    ci_affiliated.Locked = True
    ci_affiliated.BackColor = RGB(255, 255, 255)
    'primary name
    ci_name1.Value = ""
    ci_name1.Locked = True
    ci_name1.BackColor = RGB(255, 255, 255)
    ci_email1.Value = ""
    ci_email1.Locked = True
    ci_email1.BackColor = RGB(255, 255, 255)
    ci_tele1a.Value = Format(0, "000.000.0000")
    ci_tele1a.BackColor = RGB(255, 255, 255)
    ci_tele1a.Locked = True
    ci_tele1b.Value = Format(0, "000.000.0000")
    ci_tele1b.BackColor = RGB(255, 255, 255)
    ci_tele1b.Locked = True
    'secondary name
    ci_name2.Value = ""
    ci_name2.Locked = True
    ci_name2.BackColor = RGB(255, 255, 255)
    ci_email2.Value = ""
    ci_email2.Locked = True
    ci_email2.BackColor = RGB(255, 255, 255)
    ci_tele2a.Value = Format(0, "000.000.0000")
    ci_tele2a.BackColor = RGB(255, 255, 255)
    ci_tele2a.Locked = True
    ci_tele2b.Value = Format(0, "000.000.0000")
    ci_tele2b.BackColor = RGB(255, 255, 255)
    ci_tele2b.Locked = True
    
    MultiPage1.Value = 1
    MultiPage1.Visible = False
    MultiPage2.Value = 0
    Frame8.Visible = False
        
    group_1.Height = 124.5
    'If Application.WorksheetFunction.Count(ws_vh.Range("L:M")) = 0 Then
    If mri = True Then
        With CreateObject("scripting.dictionary")
            For Each Cl In ws_vh.Columns("L:M").SpecialCells(xlConstants, xlNumbers)
                If Cl <> "" Then .Item(Cl.Value) = Empty
            Next Cl
            Me.cb_mri.List = .Keys
            Me.cb_mri.ListIndex = 0
            If .Count = 1 Then Me.cb_mri.Locked = True
        End With
        cb_mri.BackColor = RGB(0, 168, 232)
        Label34.Caption = "   Select missing rental."
     End If
        
    'End With
    mbevents = True
End Sub
 
Upvote 0
This really wasn't meant to be such a challenge LOL.
I I have captured all your changes, there was some improvement. The box is no longer empty, but I still don't have the hightext text feature.
 
Upvote 0
Not quite sure why the text is not selected, as it works for me.
Does the textbox have the focus?
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Userform inconsistent responses
Replies
1
Views
194
Candyman8019
C
J
  • Solved
How to return focus to Userform textbox after process is finished.
Replies
1
Views
960
jvoss
J
C
  • Question Question
Generic Code To Highlight And Copy Any Selection Of Text On Any Textbox On Any Userform In An Application
Replies
0
Views
623
chazrab
C
R
  • Question Question
Search for values using an advanced search and update then display pivot table results in a userform listbox
Replies
1
Views
562
Rfriend
R
I
  • Solved
Select next TextBox if previous TextBox has a value
Replies
9
Views
1K
dmt32
D

Forum statistics

Threads
1,223,903
Messages
6,175,279
Members
452,630
Latest member
OdubiYouth

Share this page

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