I have this code, all works fine until reach the 3th last row.
if I debug all way thru, the right part of the row actually have the request format (as far as i know) for .columnwidths, I mean strCWidth have the format as follows
"12.5 pt;10.5pt;50.3pt"
but after hit F8 the value in ".columnwidths" eliminate the decimal point and the value change to
"125pt;105pt;;503pt".
This make the columns width to big.
I already try to set decimal separator and columns separator in System properties, control panel, language and region, numbers, and decimal symbol.
In fact if I change the decimal symbol to something wild like "|" or "°" or "¬", the decimal symbol change as well as in excel or VBA editor. and all work perfectly.
But if I close Excel and open again, no mare what i do it don't work again.
Private Sub CmbCodigo_Change()
Application.ScreenUpdating = False
Dim strCWidth As String
Dim i As Long
Dim lr As Double
Me.LixbCodigo.Clear
Sheets("General").Select
criteria = Me.CmbCodigo
lr = ThisWorkbook.Sheets("General").Cells(Rows.Count, "A").End(xlUp).Row
With Me.LixbCodigo
Me.LixbCodigo.ColumnCount = 4
.columnwidths = ""
For i = 2 To lr
If Cells(i, "D") = criteria Then
.AddItem Cells(i, "D")
.List(.ListCount - 1, 1) = Cells(i, "E")
.List(.ListCount - 1, 2) = Cells(i, "F")
' .List(.ListCount - 1, 3) = Cells(i, "G")
End If
Next i
For i = 1 To .ColumnCount
With Me.Controls.Add("Forms.TextBox.1", Name:="txtTemp" & i)
.AutoSize = True
.MultiLine = True
.WordWrap = False
.SelectionMargin = True
With .Font
.Name = LixbCodigo.Font.Name
.Size = (LixbCodigo.Font.Size)
End With
End With
Next i
For i = 0 To .ListCount - 1
Me.Controls("txtTemp1").Text = Me.Controls("txtTemp1").Text & vbCr & .List(i, 0)
Me.Controls("txtTemp2").Text = Me.Controls("txtTemp2").Text & vbCr & .List(i, 1)
Me.Controls("txtTemp3").Text = Me.Controls("txtTemp3").Text & vbCr & .List(i, 2)
Next i
separador = " ;"
For i = 1 To .ColumnCount
strCWidth = strCWidth & Me.Controls("txtTemp" & i).Width & "pt" & " separador "
lngTotalWidth = lngTotalWidth + Me.Controls("txtTemp" & i).Width
Me.Controls("txtTemp" & i).Visible = True
Me.Controls.Remove ("txtTemp" & i)
Next i
.Width = lngTotalWidth + LixbCodigo.ColumnCount + 5
Me.LixbCodigo.columnwidths = strCWidth
End With
Application.ScreenUpdating = True
End Sub
if I debug all way thru, the right part of the row actually have the request format (as far as i know) for .columnwidths, I mean strCWidth have the format as follows
"12.5 pt;10.5pt;50.3pt"
but after hit F8 the value in ".columnwidths" eliminate the decimal point and the value change to
"125pt;105pt;;503pt".
This make the columns width to big.
I already try to set decimal separator and columns separator in System properties, control panel, language and region, numbers, and decimal symbol.
In fact if I change the decimal symbol to something wild like "|" or "°" or "¬", the decimal symbol change as well as in excel or VBA editor. and all work perfectly.
But if I close Excel and open again, no mare what i do it don't work again.
Private Sub CmbCodigo_Change()
Application.ScreenUpdating = False
Dim strCWidth As String
Dim i As Long
Dim lr As Double
Me.LixbCodigo.Clear
Sheets("General").Select
criteria = Me.CmbCodigo
lr = ThisWorkbook.Sheets("General").Cells(Rows.Count, "A").End(xlUp).Row
With Me.LixbCodigo
Me.LixbCodigo.ColumnCount = 4
.columnwidths = ""
For i = 2 To lr
If Cells(i, "D") = criteria Then
.AddItem Cells(i, "D")
.List(.ListCount - 1, 1) = Cells(i, "E")
.List(.ListCount - 1, 2) = Cells(i, "F")
' .List(.ListCount - 1, 3) = Cells(i, "G")
End If
Next i
For i = 1 To .ColumnCount
With Me.Controls.Add("Forms.TextBox.1", Name:="txtTemp" & i)
.AutoSize = True
.MultiLine = True
.WordWrap = False
.SelectionMargin = True
With .Font
.Name = LixbCodigo.Font.Name
.Size = (LixbCodigo.Font.Size)
End With
End With
Next i
For i = 0 To .ListCount - 1
Me.Controls("txtTemp1").Text = Me.Controls("txtTemp1").Text & vbCr & .List(i, 0)
Me.Controls("txtTemp2").Text = Me.Controls("txtTemp2").Text & vbCr & .List(i, 1)
Me.Controls("txtTemp3").Text = Me.Controls("txtTemp3").Text & vbCr & .List(i, 2)
Next i
separador = " ;"
For i = 1 To .ColumnCount
strCWidth = strCWidth & Me.Controls("txtTemp" & i).Width & "pt" & " separador "
lngTotalWidth = lngTotalWidth + Me.Controls("txtTemp" & i).Width
Me.Controls("txtTemp" & i).Visible = True
Me.Controls.Remove ("txtTemp" & i)
Next i
.Width = lngTotalWidth + LixbCodigo.ColumnCount + 5
Me.LixbCodigo.columnwidths = strCWidth
End With
Application.ScreenUpdating = True
End Sub