[VBA] If "yes", shows cell content

[SarahChan]

Hi everyone,

I'm new in VBA and have looked into this for a week but couldn't find any solution T___T

I have defined a name to range J38:Q38 with name "Claim" with drop down list option of 'Yes' and 'No'. I would like to make this condition:

If "Claim" = 'Yes', cell AK38 shows its content which is '(compulsory)'

Else, the compulsory text in cell AK38 would be hidden/disappear. If hiding a cell content is not supported in VBA then hiding the column also acceptable.

It would be awesome if anyone could help me. I'm really new in VBA, I'm sorry

 

[tygrrboi]

1. The range "Claim" is eight cells. J38, K38, L38 etc. to Q38.
What do you mean by = Yes? Do you mean if any of these has a single yes in it? If all of these cells have a yes in them?


2. In terms of hiding the cells, How about this for an option instead of hiding a row?
You could Custom Number Format the cells to invisible or not based on the Yes/No
Invisible Format means that in the cell the text will still "exist" but it will not be shown. However, if you click that cell, the text will still appear in the formula bar.
You can test it yourself to see what it looks like. Type something into any cell, then with that cell selected, go to Custom Number Format and type ;;; into the format area.
What do you think about that as a method?

3. Would you like this to update itself whenever a cell is changed (from yes to no or vice versa)? Or would you like this to happen when the user clicks a button, like "Finish" or "Submit" for example?

If I get this information from you, I can make a suggestion as to the VBA.

 

[SarahChan]

Hi Tygrrboi,

#1. It is like this:

The name of the range is defined as "Claim". I merge&center from J38 to Q38.

Your #2 method is definitely what I wanted. Thank you for reading my mind!

#3. It will be updated when user changed "Claim" option. If it's changed to 'Yes', the text will automatically appear. If user changed to 'No' or press Delete/backspace (because it could also be blank) then it's hidden.

Thank you so much !

 

[tygrrboi]

IF you want to go the VBA route...
Go ahead and type "(Compulsory)" into AK38 and then paste this worksheet change event into the VBA editor for the sheet you are working with.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 8 Then Exit Sub
If Intersect(Target, Range("Claim")) Is Nothing Then Exit Sub
Select Case Target(1).Value
Case Is = "Yes"
    Target(1).Offset(, 20).NumberFormat = "@"
Case Else
    Target(1).Offset(, 20).NumberFormat = ";;;"
End Select
End Sub

But, if you don't and just want a formula in the cell...
Unless I'm mistaken,
This formula in AK38 should work
=If(Claim = "Yes", "(compulsory)", "")

 

[SarahChan]

It's working, thanks!! I have tried putting just formula before and it was working but I need to use VBA no matter what.

Anyway I have another question, I need to do this validator for "Claim" and "Website".

They are almost the same but the difference is that the compulsory text for "Website" will be shown in Sheet 11 named 'C11' while "Website" input is in Sheet 1 which its name is C1.

I have tried this code but failed:

Private Sub Worksheet_Change(ByVal Target As Range)If Target.CountLarge > 8 Then Exit Sub
If Intersect(Target, Range("Website")) Is Nothing Then Exit Sub
Select Case Target(1).Value
Case Is = "Ya/Yes"
Sheets("C11").Select
Target(1).Offset(, 20).NumberFormat = "@"
Case Else
Sheets("C11").Select
Target(1).Offset(, 20).NumberFormat = ";;;"
End Select
End Sub

Do you have any idea on how to produce it? Sorry for being so noob ><

 

[tygrrboi]

Ok here is what I assume.

"Claim" drop down menu is in Sheet 1 and the compulsory cell is also in Sheet 1 and the code I previously provided works.

"Website" drop down menu is also in Sheet 1 in cell C11, and the compulsory cell is on Sheet 11 (in cell C11).

If this is true... try the following.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 8 Then Exit Sub

If Not Intersect(Target, Range("Claim")) Is Nothing Then  'Checks if you have selected the "Claim" Cell
    Select Case Target(1).Value
    Case Is = "Yes"
        Target(1).Offset(, 20).NumberFormat = "@"
    Case Else
        Target(1).Offset(, 20).NumberFormat = ";;;"
    End Select
elseif not intersect(Target,Range("Website")) is nothing then  'You did not select "Claim" cell, so this checks if you selected "Website"
    Select Case Target(1).Value
    Case is = "Ya/Yes"
        Sheets("C11").Range("C11").NumberFormat = "@"
    Case else
        Sheets("C11").Range("C11").NumberFormat = ";;;"
    End Select
end if
End Sub

If they are not on the same sheet (sheet 1) then it will be different. You can keep part of what you suggested, just replace what you had after Select Case with what I have.


In general, there is often no need to select a cell or sheet in VBA. You can just reference the different cell or sheet while still maintaining the focus of your original sheet, as I did above. Use Sheets("blahblahblah").Select only when you actually want the workbook to jump to that page so the user can see it... not when you just want to collect information from it or put information in it.

I hope this helps.

If not I will try to give more advice tomorrow.