Two doubts on some (i think) simple codes

[rodtt]

Hi!
This is my first post in this community.

I have little experience with programming and even less with VBA and I'm having a little trouble with some codes:

In those first, I actually solved the problem by deleting each cell at a time,
but I wanted to do a cleaner code.

I'm getting a critic "Select Method of Range class failed"

[VBA]Sheets("Relatorio").Range("A210").Select
Selection.ClearContents
Sheets("Relatorio").Range("A1321").Select
Selection.ClearContents[/VBA]

[VBA]Sheets("Planilha").Range("B4:B7").Select.ClearContents
Sheets("Planilha").Range("B11:B15").Select.ClearContents
Sheets("Planilha").Range("B17:B18").Select.ClearContents[/VBA]

Now on this one, I'm trying to hide the range depending on what is on a cell, which is formated with a data validation, as a list.

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("B2")) Is Nothing Then
Call changeFESP
End If
Application.EnableEvents = True
End Sub

Sub changeFESP()
Select Case B2
Case FESP
For Each cell In Range("A13:C15")
cell.EntireRow.Hidden = True
Next cell
Case Estatutário
For Each cell In Range("A13:C15")
cell.EntireRow.Hidden = False
Next cell
End Select
End Sub[/VBA]

Any kind of light is here welcome.

Thanks in advance!

[GTO]

Greetings Rod,

Please allow me to be the first one to welcome you to vbaexpress

This is a great site (my first and favorite) imo, and I am sure you will be happy you joined!

Now to your questions:

[VBA]Sub ouch()
Sheets("Relatorio").Range("A210").Select
Selection.ClearContents
End Sub[/VBA]

This will not work unless 'Relatorio' is the active sheet, as you cannot Select another worksheet and a Range at the same time.

Rather than Select the sheet and then Select the range (and then ClearContents of the Selection), try like this:
[VBA]Sub NoOuch()
ThisWorkbook.Worksheets("Relatorio").Range("A210").ClearContents
End Sub[/VBA]
See, if you qualify the command so that Excel knows specifically what is to be cleared (regardless of what sheet has the focus currently), then no worries :-)

In fact, Select(ing) and/or working with the Selection is rarely necessary, and usually less optimal. As you mentioned just getting into vba, I would mention that while the macro recorder is a great tool, after recording some actions, it is almost always possible to go through the recorded code and replace bits (such as Select), increasing specificity of the code.

As to:

[VBA]Sub TooMuchAtOnce()
Sheets("Planilha").Range("B4:B7").Select.ClearContents
End Sub[/VBA]
Even if 'Planilha' is the currently selected sheet, this will fall over, as you are combining two commands in the line.

Finally, I would mention setting a reference to at least the worksheet, if not the range as well. You will gain the advantage of intellisense and find it easier to see what properties or methods are available for a particular thing...

[VBA]Sub SetAReference()
Dim MyWorksheet As Worksheet

'// Though I try and avoid over-using 'Resume Next' (which just tramples over errors, //
'// ignoring them, which can be worse later in the code), in this case, we just ignore//
'// a potential error, as if the worksheet has been deleted or renamed, the Set will //
'// fail. //
On Error Resume Next
Set MyWorksheet = ThisWorkbook.Worksheets("Planilha")
On Error GoTo 0

'// after only ignoring errors for one line, we can check if a reference was set and //
'// act accordingly. //
If Not MyWorksheet Is Nothing Then
MyWorksheet.Range("B4:B7").ClearContents
Else
MsgBox "I could not find the worksheet named ""Planilha"".", vbExclamation, "Yikes!"
Exit Sub
End If

End Sub
[/VBA]

As to the last bit of code you showed, it is hard to say whether 'B2' is a declared variable and if so, what kind. Maybe attach a small workbook to explain it better.

I would strongly encourage you to use Option Explicit, so that variable declaration is required.

Well, hopefully that is a little help at least, and again, welcome to VBAX.

Mark

[rodtt]

First of all, thanks for you kind welcome, Mark!
I hope to come here often. This looks like a nice community.

Also, thanks for your advices!

Now, on to work!

I did as you said with my two first codes and it worked nice. I wasn't aware that you had to be so specific when working with a not active worksheet, as I was not aware you couldn't combine more than one command in a single line (like python allows).

On your intellisense code, this might prove very useful to me, as this workbook is still very much a prototype and subject to changes, even on its layout.

And, finally, my last code:

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Select Case B2
Case FESP
Call closeFESP
Case Else
Call openFESP
End Select
Application.EnableEvents = True
End Sub

Sub openFESP()
For Each cell In Range("A13:C15")
cell.EntireRow.Hidden = False
Next cell
End Sub

Sub closeFESP()
For Each cell In Range("A13:C15")
cell.EntireRow.Hidden = True
Next cell
End Sub[/VBA]

As you can see, I'm working with a slightly different code, but pretty much the same.

I can't upload the archive right now, so, to answer your question, "B2" is a cell that is formated with a dropdown list for data validation: FESP, Estatutário and Extra-quadro.

The code is working, but not as expected. It's only hiding the rows, no matter what I select on B2, and also when changing any of the cells under it.

I also wanted to hide only Range "A13:C15", not the entire row.

Thanks again!

[Aussiebear]

At the moment your code calls the "closeFESP" sub and therefore it hides the rows A13:C15. If I am reading the intent correctly you need the code to hide the rows A13:C15 if the value in cell B2 is FESP and to show the rows if the value in B2 is Estatutário. This is not tested.
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("B2")) Is Nothing Then
exit Sub
If Cell B2.Value ="FESP" then
With Range("A13:C15").EntireRow.Hidden =True
else
If CellB2.Value = "Estatutário" then
With Range("A13:C15").EntireRow.Hidden = False
End With
End If
End With
End If
End If
Appplication.EnableEvents = True
End Sub [/VBA]

[snb]

This might suffice:

[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
if target.address="$B$2" then Range("A13:C15").rows.hidden=cells(2,2)="FESP"
End Sub
[/VBA]

[rodtt]

This might suffice:

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
if target.address="$B$2" then Range("A13:C15").rows.hidden=cells(2,2)="FESP"
End Sub

[/vba]

Thanks, that did the trick, eventhough I didn't understand why there is an "=" in here:
[vba]Range("A13:C15").rows.hidden=cells(2,2)[/vba]
Isn't cells(2,2)="FESP" a condition for Range("A13:C15").rows.hidden?

Also, how can I hide only Range("A13:C15"), not the entire row?

[snb]

Also, how can I hide only Range("A13:C15"), not the entire row?

You can't. Only whole rows or whole columns can be hidden.

[Aussiebear]

There is a cheap hack to this by changing the font colour to match the background colour, however if you hover the mouse of the cell the contents will show in the formula bar

[rodtt]

Yes, I was aware of that, but it will not fit what I'm trying to do.

I wanted to prevent the user from messing the spreadsheet's calculation by adding unecessary information when not needed.

Those rows will have some other important information that shouldn't be hidden, that's why I want to hide only those specific cells. I won't be able to reorganize the layout though, or it will become too vertical.

In this case, I need to lock those cells in the range.

[snb]

If you want to 'guide' users, use a userform.

[rodtt]

If you want to 'guide' users, use a userform.

I see.
I'll latter make some adjustments in my document, but since this "version" is almost finished, it would be really helpful to make a condition to block those cells and put the spredsheet on work.