Posts by Woody93

    Hi All.
    I am importing some data from an old workbook to a new one. In the new workbook sheet, where the data shall be, I have a commandbottom (only visible this half of the year) with the following code:

    Private Sub OldWeeks_Click()

    Application.ScreenUpdating = False
    Select Case OldWeeks.Caption
    Case "Show all weeks"
    Columns("D:GJ").Hidden = False
    For i = 4 To 192
    If Cells(4, i).Value = "" Or (Cells(2, i).Value = "Su" And Worksheets("Medarbejdere").Cells(50, 3).Value = "No") Then Columns(i).Hidden = True
    Next i
    OldWeeks.Caption = "Hide old weeks"
    Case "Hide old weeks"
    Dato = DateValue("1/" & 6 * Left(Me.Name, 1) - 5 & "/" & Right(Me.Name, 4))
    Dato = Dato - Weekday(Dato)
    i = 4
    Do Until Dato = Date - Weekday(Date) or i>192
    Columns(i).Hidden = True ’’’’’’’’’ Error in this line ’’’’’’’’’’’’’’’’’’’
    Dato = Dato + 1
    i = i + 1
    OldWeeks.Caption = "Show all weeks"
    End Select
    Cells(ActiveCell.Row + 1, ActiveCell.Column + 1).Activate
    Application.ScreenUpdating = True

    End Sub

    When I am copying a lot of data I will get a Run-time error ’1004’ in the columns(i).Hidden =True. (the value of i is around 33 and 80)
    Why do I get this error???

    Help please.


    Hi Chris,

    Excel gives the new sheet the generic name, The old sheets name + a number
    E.g. Sheets(“Temp”).Copy After:=Sheets("Amenity") gives a sheet with the name “Temp (2)” and copy again results in “Temp (3)”
    So if you want to make a new sheet for each unit number in the column B and name them after the cell values in the column, you could write

    Have a nice day


    Thanks for your advise Dave, but I hav tried them, and it seem not to work that way.

    With your advise (where I put in the Protection-line, remove my unprotection, and replace my cells.validation.delete with yours .delete). I will get the error-message everytime.

    It seem to need the unprotection make the validation, or?

    What else could be wrong? Perhaps a setup-thing?


    PS. I have made an On Error Goto -> MsgBox "There is a problem with ..."
    But It isn't a solution.


    No. If I don't Unprotect. The Validation gives an error everytime!
    But should this work?

    Me.Unprotect Password:="xxx"

    With Range(Cells(i, 29), Cells(i, 34)).Validation


    end with

    Me.Protect Password:="xxx", DrawingObjects:=False, contents:=True, Scenarios:=True, userinterfaceonly:=True

    It hasn't made any mistakes yet.
    But what is the difference for the validation code? I can't see any.



    When I activate my Worksheet ‘Medarbejdere’, I get an error-message.
    “Runtime error ‘1004’. Application – defined or object – defined error.”
    It comes in the line
    .Add Type:=…
    I can’t see why it comes? And it is only sometimes the error comes! Can You?

    Private Sub Worksheet_Activate()

    Application.ScreenUpdating = False
    Range(Cells(7, 29), Cells(46, 34)).Locked = False
    ‘ making the list/name ‘Ruter’
    i = 2
    Do Until Worksheets("Rutetider").Cells(i, 1).Value = ""
    Worksheets("Rutetider").Cells(i + 2, 43).Value = Worksheets("Rutetider").Cells(i, 1).Value
    i = i + 1
    Names.Add Name:="Ruter", RefersTo:="=Rutetider!$AQ$2:$AQ$" & i + 1
    Unprotect Password:="xxx"
    ‘ making the dropdownlist in the cells
    For i = 7 To 46
    If Cells(i, 27).Value <> "" And Cells(i, 27).Value <> "Ledig" Then
    With Range(Cells(i, 29), Cells(i, 34)).Validation
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Ruter"
    .InCellDropdown = True
    .ShowInput = False
    .ShowError = False
    End With
    End If
    Next i
    Me.Protect Password:="xxx", userinterfaceonly:=True
    Application.ScreenUpdating = True

    End Sub



    Can you use FormatConditions?

    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""1"""
    Selection.FormatConditions(1).Font.ColorIndex = 3
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, Formula1:="=""1"""
    Selection.FormatConditions(2).Interior.ColorIndex = 41

    here if the cellvalue is 1 the fontcolor becomes red, and if the cellvalue <> 1 the interiorcolor becomes blue.



    I have an Excel workbook, where when the user want to update, then my VBProject shall copy a sheet over to another workbook (Old Sheets). In this workbook I don't want the sheets makros to be played. :)
    So I tried to remove the makros from the sheets in the programme, but I get the error, that my VBProject is protected :(
    How can I unprotect my VBProject in a makro?