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
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
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
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:
Open in new window
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::
Open in new window