Textboxs & combo Boxs in usefrom to assign them to sheet

Amro El ghazawei

New Member
Joined
Feb 27, 2021
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
Hi,

i create a user from for projects data , with a lot of text boxes & combo boxes, i named each text box & combo box with different names like PO_value , PO_rev .....etc instead of textbox 1,2,3 ....etc.

i assign each textbox/combo box to a column in sheet , using the normal way like

.Cells(iRow, 2) = frmForm.PO_SCOPE.Value
.Cells(iRow, 3) = frmForm.PO_SECTOR.Value

as i said i have more than 200 test box/combo box i wrote the code for the remaining and it work fine , but it takes time for processing it appears like the excel is frozen.

i was thinking to use an array for this assignment but i am not so familiar with array issues
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
hi and welcome to MrExcel.
have you disabled screenupdating and xlcalculation. this will speed up execution. also there are shorter coding methods you could use, but you would have to redraw the textboxes so they have the original names again
 
Upvote 0
HI Didi

thanks for your response ,

for screen updating i already done it , but for xlcalcuation could you advise.

if i have no option rather than getting text box to their intial i will , it's a huge effort but i want to keep it as a last option.

thanks for advance
 
Upvote 0
At the start of your macro in same place as your screenupdating code
VBA Code:
Application.Calculation = xlManual
At the end
VBA Code:
Application.Calculation = xlAutomatic
it may not be that hard to rename the textboxes. if you redraw then, the numbering will start at TextBox1 so just draw and replace.
there is another option you can try. there is a default order the textboxes are referenced by VBA. you could find out the order using the code below and then it would be possible to save all in the default order to a sheet.
this sub will list all the controls in column A of the current sheet

VBA Code:
Sub ShowTextBoxOrder()
    Dim Ctrl As MSForms.Control, CtrlNum As Long
    
    CtrlNum = 0
    For Each Ctrl In Me.Controls
        CtrlNum = CtrlNum + 1
        Cells(CtrlNum, 1) = Ctrl.Name
    Next
End Sub
 
Upvote 0
Dear Didi

thanks for your reply , i used the Application. Calculation and it works very nice.
thanks again.
 
Upvote 0
Hi Didi,

following my post,
my user from contains numbers of textboxes & combo boxes, in multipage , i managed to rename some of them to be textbox 100 to 115 am wondering if i can validate the data entry for these 15 textboxes to be only numbers with decimal points. instead of using write the code for each one.

thanks
 
Upvote 0
textboxes do not have inbuilt data validation, but there are a couple of options you could try... one is to write a function that checks the data, and then use it over and over for each textbox_change. if there are going to be many of these to check then there is another way that has way less code but is harder to understand. but is what you are asking to do.
 
Upvote 0
textboxes do not have inbuilt data validation, but there are a couple of options you could try... one is to write a function that checks the data, and then use it over and over for each textbox_change. if there are going to be many of these to check then there is another way that has way less code but is harder to understand. but is what you are asking to do.
HI
thanks for your reply & sorry for replying late
well the project i am working on it is really huge, so i am thinkind on all best way for coding and not to slow the processing , so i am open for any ideas.
thanks again.
 
Upvote 0
In your userform module:

VBA Code:
Dim POs() As New POText  ' put this at the top before any subs

Sub Userform_initialize
    ReDim POs(0 To 15)  'POtext class
    For Row = 0 To 15
        Set POs(Row).TB = Controls("Textbox" & Row + 100) ' assign the textboxes to the POs collection
    Next Row
   .  .  .  .


Make a Class Module. Call it POText

VBA Code:
Public WithEvents TB As MSForms.TextBox

Sub TB_change()
    SelTB = Val(Mid(TB.Name, 8) ' the number of the textbox you have changed
    MsgBox "textbox " & SelTB & " has been changed"
    ' do validation etc in here
End Sub

any textboxes you assign with Set POs( ) will be controlled by this Class
 
Upvote 0
thanks again

i am working on your code,

i have another point is i need your help on it ,

i have cells text as follow Q0.0, Q0.1 , Q0.2...etc. where it has letter & decimal number, i need to highlight the cell if by mistaken the number after decimal point is greater than 7.
thanks in advance.
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

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