Range( _ "AK6: AK" & lrow), Sort On:=xl Sort On Values, Order:=xl Ascending, Data Option:= _ xl Sort Normal With ws.
After the code is executed, everything is turned back on.
As a result, the performance is increased from 10% to 500% (aha, the macro works 5 times faster if it continuously manipulates the cells' contents).
Save your workbook as "Excel macro-enabled workbook".
Press , then click the "No" button in the "The following features cannot be saved in macro-free workbook" warning dialog. Choose "Excel macro-enabled workbook" from the "Save as type" drop-down list and click the Save button.
I tried to modify your code to remove any Select/Activate command in my opinion you don't need to unprotect the Sheet8 for the Text Box to update.
When the selection in Combo Box7 changes, Macro41 changes cells values in Sheet17, a Text Box (Active X Control) located below Combo Box7 in Sheet8 reads from the new cell value in Sheet17. After further experimentations, I discovered that you don't even need the sheet to be visible to operate on it. Screen Updating = True err Handle: End Sub Sub Macro41() Dim ws As Worksheet Set ws = Sheet17 Dim lrow As Long lrow = ws.
Add Key:=Range( _ "AK6: AK" & lrow), Sort On:=xl Sort On Values, Order:=xl Ascending, Data Option:= _ xl Sort Normal With Active Workbook.
Try the followings : Private Sub Combo Box7_Change() On Error Go To err Handle: Application.
It is a very safe way to refer to worksheets as the sheet name could possibly be changed in future for any reason and if this does happen, the code is dead. Hi, Sheet8 is the worksheet that Combo Box7 resides in, along with the other items decribed above, charts etc.
If you look at the sheet list in the VB Editor, you'll notice that each sheet has a name in parentheses and the sheet code directly to the left.
Screen Updating incorporated but the screen still flashes/flickers when the code runs - everything (charts/tables etc.) momentarily disappear and re-appear when it runs so it doesn't look very slick. Private Sub Combo Box7_Change() On Error Go To err Handle: Application. When the selection in Combo Box7 changes, Macro41 changes cells values in Sheet17, a Text Box (Active X Control) located below Combo Box7 in Sheet8 reads from the new cell value in Sheet17.