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

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

In article <(E-Mail Removed)>,
(E-Mail Removed) wrote:

> 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

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

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.


In article <(E-Mail Removed)>,
(E-Mail Removed) wrote:

> 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

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??

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.

In article <(E-Mail Removed)>,
(E-Mail Removed) wrote:

> 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??

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.

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.

In article <(E-Mail Removed)>,
(E-Mail Removed) wrote:

> Sorry, my sheets were protected, actually they have to stay protected.
> Sorry.
>
> Youre code worked ok when I don't protect them.