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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,297
Office Version
  1. 2010
Platform
  1. Windows
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
 

Amro El ghazawei

New Member
Joined
Feb 27, 2021
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
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
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,297
Office Version
  1. 2010
Platform
  1. Windows
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
 

Amro El ghazawei

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

ADVERTISEMENT

Dear Didi

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

Amro El ghazawei

New Member
Joined
Feb 27, 2021
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
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
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,297
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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.
 

Amro El ghazawei

New Member
Joined
Feb 27, 2021
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
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.
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,297
Office Version
  1. 2010
Platform
  1. Windows
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
 

Amro El ghazawei

New Member
Joined
Feb 27, 2021
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,514
Messages
5,636,785
Members
416,940
Latest member
JohanT

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
Top