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
Loop
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.

Woody

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

Sub Sheet_Create()


For i = 2 To 3 ' where n is the number of rows with a unit number
Sheets("Temp").Copy After:=Sheets("Amenity")
ActiveSheet.Name = Sheets("Temp").Range("B" & i).Value ' no s at the end of ActiveSheet
Next i


End Sub


Or if the only values in column B are the unit numbers You could do like this


Sub Sheet_Create() 


i = 2
do while Sheets("Temp").Cells(i,2).value<>""
Sheets("Temp").Copy After:=Sheets("Amenity") 
ActiveSheet.Name = Sheets("Temp").Range("B" & i).value 
i=i+1
Loop


End Sub

Have a nice day

Woody

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?

Woody

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

Hi,

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.

Woody93

Hi,

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
Loop
Names("Ruter").Delete
Names.Add Name:="Ruter", RefersTo:="=Rutetider!$AQ$2:$AQ$" & i + 1
Unprotect Password:="xxx"
Cells.Validation.Delete
‘ 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

Thanks.

Woody93

Can you use FormatConditions?

Selection.FormatConditions.Delete
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.

Woody93

Hi,

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?

Thanks

Woody93