Alternative to select function in VBA?
I use a excel woorkbook with some VBA in it and are excerpering a problem. The workbook works great when it's the only workbook open, but when other workbooks also are open, my workbook tend to crash when switching between workbooks and entering data in the cells.
I don't really know what's causing the problem, but I suspect it to be something with the use of SELECT. Therefore I want to get rid of the select functions to test if that is causing the crashes.
The code is attached to dropdown list (using datavalidation), there the users can choose between different types of how to show data:
Any alternatives to the SELECT function?
I don't really know what's causing the problem, but I suspect it to be something with the use of SELECT. Therefore I want to get rid of the select functions to test if that is causing the crashes.
The code is attached to dropdown list (using datavalidation), there the users can choose between different types of how to show data:
If Target.Address = "$C$10" Then
Range("C28:D89,I28:J89,O28:P89,U28:V89").Select
Select Case Cells(10, 6)
Case 0
Selection.NumberFormat = "0"
Case 1
Selection.NumberFormat = "0.0"
Case 2
Selection.NumberFormat = "0.00"
Case 5
Selection.NumberFormat = "0%"
Case 6
Selection.NumberFormat = "0.0%"
Case 7
Selection.NumberFormat = "0.00%"
End Select
Range("C10").Activate
End If
Any alternatives to the SELECT function?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Well since you have not shared the full code, just to give you an idea here are some points....
Qualify the cell and range reference with the workbook and sheet to work properly.
Like Range("C28:D89,I28:J89,O28
And your Select Case statement should be something like this....
With above changes the code will always be executed on the workbook where the code resides. Of course you may change the sheet reference as per your requirement.
Qualify the cell and range reference with the workbook and sheet to work properly.
Like Range("C28:D89,I28:J89,O28
ThisWorkbook.Sheets("Sheet1").Range("C28:D89,I28:J89,O28:P89,U28:V89").Select
And your Select Case statement should be something like this....
Select Case ThisWorkbook.Sheets("Sheet1").Cells(10, 6)
With above changes the code will always be executed on the workbook where the code resides. Of course you may change the sheet reference as per your requirement.
just saw that Sktneer beat me to it (again!)
Its more likely that the problem occurs because you aren't pointing explicitly to the appropriate sheet.
to test this theory trying adding 'activesheet' in front of the Range method as in:
However this assumes the currently active sheet is the one you are working with. to be more robust you would define the workbook and worksheet as variables and refer to them specifically eg::
Its more likely that the problem occurs because you aren't pointing explicitly to the appropriate sheet.
to test this theory trying adding 'activesheet' in front of the Range method as in:
activesheet.Range("C28:D89,I28:J89,O28:P89,U28:V89").Select
However this assumes the currently active sheet is the one you are working with. to be more robust you would define the workbook and worksheet as variables and refer to them specifically eg::
Dim allData As Worksheet 'the output sheet
Dim theWorkbook As Workbook 'the workbook containing the sheet
Set theWorkbook = ActiveWorkbook 'or set to a specific workbook
Set allData = theWorkbook.Sheets("All") ' ALL is the output sheet - modify according to tab name
theWorkbook.allData.Range("C28:D89,I28:J89,O28:P89,U28:V89").Select
....
Nackey,
Though i agree with sktneer that it's always advisable to make the code as specific as you can to make it more effective...
But what baffled me is that i'm not able to understand how the code is getting triggered in some other workbook because i'm assuming you are calling this by change event..and now if you are calling in the change event the code will only will execute when you are in that workbook..I mean you are actually have that workbook as active.. it doesn't matter if you have 10 different workbooks open and you are working on others..it shouldn't call the code of yours because that doesn't trigger the change event that you are referring here..
Can you advise me how you are calling or triggering this code...
Saurabh...
Though i agree with sktneer that it's always advisable to make the code as specific as you can to make it more effective...
But what baffled me is that i'm not able to understand how the code is getting triggered in some other workbook because i'm assuming you are calling this by change event..and now if you are calling in the change event the code will only will execute when you are in that workbook..I mean you are actually have that workbook as active.. it doesn't matter if you have 10 different workbooks open and you are working on others..it shouldn't call the code of yours because that doesn't trigger the change event that you are referring here..
Can you advise me how you are calling or triggering this code...
Saurabh...
I've a pretty good feeling about the select code not been specific enough to the workbook as both of you suggested, so I'll try test that tomorrow.
And to clearify some things:
The users select input type from a dropdown list and that changes the dataformat of the cells below as shown in the picture.
Data is used in a graph in another worksheet.
The full code isn't a secret and in case you are interested the full code from this_workbook is:
and code from specific wooksheet:
If you have any suggestions for tidying up the code it would be much appreciated as I have taken over much of the code from someone else and I'm not an expert by no means. BUT my concern rigth now is getting rid of the crashes, and then the other things can be done later.
And to clearify some things:
The users select input type from a dropdown list and that changes the dataformat of the cells below as shown in the picture.
Data is used in a graph in another worksheet.
The full code isn't a secret and in case you are interested the full code from this_workbook is:
Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
With Ark7.PageSetup
.PrintArea = "$A$1:$O$37"
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
End With
With Ark8.PageSetup
.PrintArea = "$A$1:$O$37"
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
End With
With Ark9.PageSetup
.PrintArea = "$A$1:$O$37"
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
End With
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Skjuler diverse ark
Ark2.Visible = xlSheetHidden
Ark4.Visible = xlSheetHidden
Ark5.Visible = xlSheetHidden
Dim txtFileName As String
'1. Check of Save As was used
If SaveAsUI = True And Application.UserName <> "my name" Then
Cancel = True
'2. Call up your own dialog box. Cancel out if user Cancels in the dialog box
txtFileName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As XLSM file")
If txtFileName = "False" Then
'MsgBox "You didn't save", vbOKOnly
Cancel = True
Exit Sub
End If
'3. Save the file.
Application.EnableEvents = False
ThisWorkbook.SaveAs Filename:=txtFileName, FileFormat:=52
Application.EnableEvents = True
End If
'If Application.UserName <> "my username" Then
'Exit Sub
'Else
'Dim txtFileName As String
' If SaveAsUI = True Then
' Cancel = True
' txtFileName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As XLSM file")
' If txtFileName = "False" Then
'MsgBox "Action Cancelled", vbOKOnly
' Cancel = True
' Exit Sub
' End If
' Application.EnableEvents = False
' ThisWorkbook.SaveAs Filename:=txtFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
' Application.EnableEvents = True
' End If
'End If
End Sub
Private Sub Workbook_Open()
'Sørger for at det altid er ark1 som der ses når regnearket åbnes
Ark1.Activate
End Sub
'Private Sub Workbook_WindowActivate(ByVal Wn As Window)
'Dim oCtrl As Office.CommandBarControl
'If Ark1.Cells(100, 1) = 0 Then
'Disable all Cut menus
' For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
' oCtrl.Enabled = False
' Next oCtrl
'Application.CellDragAndDrop = False
'Application.OnKey "^x", ""
'End If
'End Sub
'Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
'Dim oCtrl As Office.CommandBarControl
'Enable all Cut menus
' For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
'
' oCtrl.Enabled = True
' Next oCtrl
'Application.CellDragAndDrop = True
'Application.OnKey "^x"
'End Sub
and code from specific wooksheet:
Private Sub Worksheet_Change(ByVal Target As Range)
'Låser arket op
Ark1.Unprotect Password:=1234
'Styrer input format
If Target.Address = "$C$10" Then
Range("C28:D89,I28:J89,O28:P89,U28:V89").Select
Select Case Cells(10, 6)
Case 0
Selection.NumberFormat = "0"
Case 1
Selection.NumberFormat = "0.0"
Case 2
Selection.NumberFormat = "0.00"
Case 5
Selection.NumberFormat = "0%"
Case 6
Selection.NumberFormat = "0.0%"
Case 7
Selection.NumberFormat = "0.00%"
End Select
Range("C10").Activate
End If
'Styrer output format
If Target.Address = "$C$11" Then
Range("C16:C17,C19,E28:E89,K28:K89,Q28:Q89,W28:W89").Select
Select Case Cells(11, 6)
Case 0
Selection.NumberFormat = "0"
Case 1
Selection.NumberFormat = "0.0"
Case 2
Selection.NumberFormat = "0.00"
Case 5
Selection.NumberFormat = "0%"
Case 6
Selection.NumberFormat = "0.0%"
Case 7
Selection.NumberFormat = "0.00%"
End Select
Range("C11").Activate
End If
'Styrer om input er tekst eller dato
If Target.Address = "$C$13" Then
Range("B28:B89,H28:H89,N28:N89,T28:T89").Select
Select Case Cells(13, 6)
Case 0
Selection.NumberFormat = "General"
Case 1
With Selection
.NumberFormat = "mmm/yyyy"
.HorizontalAlignment = xlLeft
End With
End Select
Range("C13").Activate
' Ark4.Calculate
End If
If Cells(19, 8) = "" Then
Cells(19, 8) = "Vælg"
' Ark4.Calculate
End If
If Cells(10, 3) = "" Then
Cells(10, 3) = "Procent (0 decimaler)"
' Ark4.Calculate
End If
If Cells(11, 3) = "" Then
Cells(11, 3) = "Procent (0 decimaler)"
' Ark4.Calculate
End If
If Cells(13, 3) = "" Then
Cells(13, 3) = "Tekst"
' Ark4.Calculate
End If
'Låser arket
Ark1.Protect Password:=1234
End Sub
If you have any suggestions for tidying up the code it would be much appreciated as I have taken over much of the code from someone else and I'm not an expert by no means. BUT my concern rigth now is getting rid of the crashes, and then the other things can be done later.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial