1

I have a problem with my ActiveX Control ListBox. It seems to be inactive after the worksheets.activate command.

Here is what I'm doing:

  1. In the Sheet1 I have two buttons to trigger different vba codes and an ActiveX Control ListBox, used to generate a string needed for the second calculation. When I move the mouse over the ListBox the cursor turns from a cross to an arrow, so I can select the boxes.

  2. My first button triggers a vba-based import of data. The data is stored in a Sheet2. After that I activate again Sheet1 (Sheet1.Activate). After that it is no longer possible to select the boxes in the ListBox (the cursor no longer changes from a cross to an arrow when moved over the ListBox).

If I get rid of the command Sheet1.Activate, after the data-import the ActiveSheet is obviously Sheet2. Strange thing is that after selecting Sheet1 in excel the ListBox is still working. So it seems to me that the command worksheets.activate somehow deactivates the ListBox. Does anyone have an idea how to solve this problem?

That would be the code of the import script:

Sub import_stuff()
 
''defining variables
Dim panel As Worksheet
 
''turn off screen updating and displaying alerts
Application.ScreenUpdating = False
Application.DisplayAlerts = False
 
''defining the used workbooks
Set panel = ThisWorkbook.Worksheets("Controll Panel")
Set inputbook = Workbooks.Open("link to file")
 
''delete the worksheet "input data" if it exists, if not it does nothing...
On Error Resume Next
    thisbook.Worksheets("Input data").Delete
On Error GoTo 0
 
''copying the data from the source file to a new worksheet
inputbook.Worksheets("Sheet1").copy After:=panel
''renaming the newly generated worksheet
ActiveSheet.name = "Input data"
 
''closing the source file without saving
inputbook.Close False
 
panel.Activate
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Thanks in advance!

Sam
  • 11
  • 4
  • posting the code you're using helps us to helping you. – Elmer Dantas Sep 13 '17 at 14:18
  • 1
    You are right @ElmerDantas , the code is now added. – Sam Sep 13 '17 at 14:26
  • It could be due to a bug in Excel associated with ActiveX controls which is preventing you from selecting values in Listbox. Try to enter any cell on Sheet1 (with a doubleclick or F2) and confirm it with enter. After that you should be able to click on ActiveX control... read more about this issue [here](https://stackoverflow.com/questions/27411399/microsoft-excel-activex-controls-disabled) – Kresimir L. Sep 13 '17 at 14:38
  • Might be worth trying to select a cell in the sheet rather than attempting to activate the sheet and then see if you can still interact with your control – Zac Sep 13 '17 at 15:31

0 Answers0