Who can help me with a VBA

[blommerse]

Hello everybody,

Who can make a VBA code for me???
I have an sheet called "insertsheet", in cell B8 people can fill in 1
till 10 with validation.
In a other sheet called "Dataprocessing" I want row 23 is visible when
B8 is 1
I want row 23 + 24 visible when B8 is 2 etc.

At the same time I want the same in a sheet called "List" but then
start at row 27

Who can help me.

Thanks in advanced.

Regards,

BL

 

[JE McGimpsey]

One way:

Put this in your insertsheet code module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const nMAX As Long = 10
Dim nRows As Long
nRows = Me.Range("B8").Value
With Sheets("Dataprocessing").Range("23:23")
.Resize(nMAX).EntireRow.Hidden = True
.Resize(nRows).EntireRow.Hidden = False
End With
With Sheets("List").Range("27:27")
.Resize(nMAX).EntireRow.Hidden = True
.Resize(nRows).EntireRow.Hidden = False
End With
End Sub

 

[blommerse]

It lookslike it should work, only one problem.

I already have an code in the insertsheet code module.
What do I have to change, so it will work.
Thanks for your fast reply.

Regards,
BL

 

[JE McGimpsey]

If you already have a Worksheet_Change() sub, then combine the code.
Couldn't begin to tell you how without knowing what your existing code
is.

If the code in your worksheet module isn't a Worksheet_Change() sub,
then just add the sub I suggested.

 

[blommerse]

Now I have this in my code. Is this the correct way??

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B8")) Is Nothing Then
Application.ScreenUpdating = False
Range(Cells(1, 5), _
Cells(1, Columns.Count)).EntireColumn.Hidden = False
Range(Cells(1, Range("B8").Value * 2 + 6), _
Cells(1, Columns.Count)).EntireColumn.Hidden = True
End If

Const nMAX As Long = 10
Dim nRows As Long
nRows = Me.Range("B8").Value
With Sheets("Dataprocessing").Range("23:23")
..Resize(nMAX).EntireRow.Hidden = True
..Resize(nRows).EntireRow.Hidden = False
End With
With Sheets("List").Range("27:27")
..Resize(nMAX).EntireRow.Hidden = True
..Resize(nRows).EntireRow.Hidden = False
End With
End Sub

I don't think, cause I get an error.
Can you make it right??

 

[JE McGimpsey]

What error do you get?

Your code works fine for me. I'd probably rearrange it a little:

Private Sub Worksheet_Change(ByVal Target As Range)
Const nMAX As Long = 10
Dim nRows As Long

Application.ScreenUpdating = False
With Me
With .Range("B8")
If Intersect(Target(1), .Cells) Is Nothing Then Exit Sub
nRows = .Value
End With
.Range(.Cells(1, 5), .Cells(1, _
.Columns.Count)).EntireColumn.Hidden = True
.Range(Cells(1, 5), .Cells(1, _
nRows * 2 + 6)).EntireColumn.Hidden = False
End With
With Sheets("Dataprocessing").Range("23:23")
.Resize(nMAX).EntireRow.Hidden = True
.Resize(nRows).EntireRow.Hidden = False
End With
With Sheets("List").Range("27:27")
.Resize(nMAX).EntireRow.Hidden = True
.Resize(nRows).EntireRow.Hidden = False
End With
End Sub


but that's mostly a matter of taste.

 

[blommerse]

Sorry, my sheets were protected, actually they have to stay protected.
Sorry.

Youre code worked ok when I don't protect them.

Thanks for your help.

 

[JE McGimpsey]

Then you could unprotect them, then reprotect them within the macro. Or
you could use the Workbook_Open() event macro to set the
UserInterfaceOnly property to protect from user input, but allow macros
to operate.