Quick Excel Help

    First Previous
  • afray 31 May 2007 14:14:38 2,248 posts
    Seen 5 years ago
    Registered 17 years ago
    Hi guys,

    I've got what appears to be a simple Excel problem but I can't see a way to fix it. Given a cell containing text, if that text is over or equal X chars long I want to change the cell format or similarly highlight it. If it is not then I want to leave it with the default formatting.

    So:

    =IF(LEN(A1)>=X, *highlight*, *lowlight*)

    But what goes in the highlight/lowlight sections?

    Cheers in advance.
  • FairgroundTown 31 May 2007 14:16:13 2,522 posts
    Seen 8 years ago
    Registered 16 years ago
    afray wrote:
    Hi guys,

    I've got what appears to be a simple Excel problem but I can't see a way to fix it. Given a cell containing text, if that text is over or equal X chars long I want to change the cell format or similarly highlight it. If it is not then I want to leave it with the default formatting.

    So:

    =IF(LEN(A1)>=X, *highlight*, *lowlight*)

    But what goes in the highlight/lowlight sections?

    Cheers in advance.
    Look up "Conditional Formatting"
  • afray 31 May 2007 14:17:44 2,248 posts
    Seen 5 years ago
    Registered 17 years ago
    Ah ah! "Dynamic formatting" was what I was trying. Conditional has got me straight into a tutorial.

    /gives jaffa cake
  • MrWorf 25 Jun 2007 15:25:21 64,172 posts
    Seen 3 days ago
    Registered 20 years ago
    Hello!

    I've got a similar problem. I'm trying to have it so if I type "N" into a cell it will hide the whole row. Is this possible?

    So far it's =IF(D15="Y",

    Is there a code to hide a row?
  • opalw00t 25 Jun 2007 15:28:33 12,836 posts
    Seen 19 hours ago
    Registered 17 years ago
    Dunno - could a macro help?
  • Lutz 25 Jun 2007 15:28:51 48,870 posts
    Seen 4 years ago
    Registered 18 years ago
    You can't use a formula to do that, you'll need a macro. And I'm damned if I know how to do it.
  • Tweakmonkey 25 Jun 2007 15:29:17 1,300 posts
    Seen 1 year ago
    Registered 18 years ago
    Not sure but sounds like a job for a VBA macro
  • MrWorf 25 Jun 2007 15:32:25 64,172 posts
    Seen 3 days ago
    Registered 20 years ago
    ;_;

    So an active X control?

    /deletes "Advanced Excel Skills" from CV
  • MikeP 25 Jun 2007 15:36:21 2,975 posts
    Seen 1 month ago
    Registered 18 years ago
    Razz wrote:
    Hello!

    I've got a similar problem. I'm trying to have it so if I type "N" into a cell it will hide the whole row. Is this possible?

    So far it's =IF(D15="Y",

    Is there a code to hide a row?

    Try this http://exceltips.vitalnews.com/
    Pages/T0075_Hiding_Rows_Based_on_a_Cell_Value.html

    (Formatting/linking borked) if I don't add a line break.

    Edited by Mike P at 15:39:51 25-06-2007
  • pauleyc 25 Jun 2007 15:36:57 4,548 posts
    Seen 5 hours ago
    Registered 20 years ago
    Um, it seems you'd indeed need a VBA macro to do that but how to trigger it based on a changed cell content - no idea.

    You could install an ActiveX control - a button for instance - that would trigger a macro hiding the column based on the input of a cell/range though. The code for a command button would be very simple:
    [code]Private Sub CommandButton1_Click()

    Columns("A:A").Select
    Selection.EntireColumn.Hidden = True

    End Sub[/code]
    multiple edit: Doh. With an additional 'if' condition though. Or try Mike's link (once he sorts it out ;-).

    Edited by pauleyc at 15:40:03 25-06-2007
  • MrWorf 25 Jun 2007 15:52:36 64,172 posts
    Seen 3 days ago
    Registered 20 years ago
    Wow! Cheers mate! :)

    It works great! :)

    [code]Private Sub CommandButton1_Click()

    Rows("15:18").Select
    Selection.EntireRow.Hidden = True

    End Sub[/code]

    That's it for now, but I need to be able to undo it as well. As,atm, it only hides the cell and I'm unable to recall them! Please help.

    /is usless at VB :/

    EDIR

    Actually if just seems to hide the rows without letting me click it first. I tried changing it to a toggle button but to no avail, it seems I need some kind of statment that tells Excel:

    IF I click this button THEN hide those selected rows
    IF I click it again THEN unhide those rows.

    No idea where to begin. :/ Please help



    Edited by Razz at 15:59:38 25-06-2007
  • pauleyc 25 Jun 2007 16:00:05 4,548 posts
    Seen 5 hours ago
    Registered 20 years ago
    In this case try maybe a check box control (dirty solution):

    [code]Private Sub CheckBox1_Click()

    Application.ScreenUpdating = False
    'the above is to prevent Excel from displaying the actual "disappearing column" changes - useful for more operations

    If CheckBox1.Value = True Then
    Rows("15:18").Select
    Selection.EntireRow.Hidden = True
    Else
    Rows("15:18").Select
    Selection.EntireRow.Hidden = False
    End If

    End Sub[/code]

    Edited by pauleyc at 16:01:01 25-06-2007
  • MrWorf 25 Jun 2007 16:05:28 64,172 posts
    Seen 3 days ago
    Registered 20 years ago
    Cool! Cheers again, I'll give it a try just as soon as Excel uncrashes itself. :/

    *crosses fingers*
  • MrWorf 25 Jun 2007 16:20:08 64,172 posts
    Seen 3 days ago
    Registered 20 years ago
    Works a charm! :D

    I owe you a pint and a tube of jaffas :)

    Just one more thing, is there a way to embed the control into a cell?
  • pauleyc 25 Jun 2007 16:30:19 4,548 posts
    Seen 5 hours ago
    Registered 20 years ago
    Well, that's the catch - no way to embed it in a cell, at least according to my knowledge. You'd need to keep the checkbox someplace handy.
  • MrWorf 25 Jun 2007 16:48:40 64,172 posts
    Seen 3 days ago
    Registered 20 years ago
    What about this

    [code]
    Private Sub Checkbox1_Click()

    Application.ScreenUpdating = False
    'the above is to prevent Excel from displaying the actual "disappearing column" changes - useful for more operations

    If Checkbox1.Value = True Then
    Rows("15:18").Select
    Selection.EntireRow.Hidden = True
    Else
    Rows("15:18").Select
    Selection.EntireRow.Hidden = False
    End If
    End Sub

    Sub Hide()
    Rows(15, 16, 17, 18).EntireRow.Hidden = True
    ActiveSheet.Shapes("CheckBox1").Visible = False
    End Sub

    Sub UnHide()
    Rows(15, 16, 17, 18).EntireRow.Hidden = False
    ActiveSheet.Shapes("CheckBox1").Visible = True
    End Sub[/code]

    In theory it should hide the checkbox once it's clicked. But it does nothing. Any ideas?
  • JetSetWilly 25 Jun 2007 17:01:11 5,720 posts
    Seen 1 year ago
    Registered 16 years ago
    Shouldn't:

    ActiveSheet.Shapes("CheckBox1").Visible = False

    be moved into Checkbox1_Click() because Hide() isn't associated with the checkbox control?

    If you hide the checkbox when it's checked how will you ever unhide it?
  • pauleyc 25 Jun 2007 17:01:37 4,548 posts
    Seen 5 hours ago
    Registered 20 years ago
    The way it's formulated now both subroutines are separate from the checkbox object. They should be available as single macros to run, named Hide and UnHide respectively.

    You could move the 'ActiveSheet.Shapes("CheckBox1").Visible = False/True' clause into the checkbox conditions but it'd stay hidden after the first click. :-)
  • MrWorf 25 Jun 2007 17:14:55 64,172 posts
    Seen 3 days ago
    Registered 20 years ago
    JSW: Unhiding it isn't a problem, once it's hidden the rows won't be needed.

    pauleyc: I've tried mate but I think I'm doing something wrong as the checkbox still doesn't disapear. :/

    I.E

    [code]
    Private Sub Checkbox1_Click()

    Application.ScreenUpdating = False
    'the above is to prevent Excel from displaying the actual "disappearing column" changes - useful for more operations

    If Checkbox1.Value = True Then
    Rows("15:18").Select
    Selection.EntireRow.Hidden = True
    Else
    Rows("15:18").Select
    Selection.EntireRow.Hidden = False
    End If

    If Rows(15).EntireRow.Hidden = True Then
    ActiveSheet.Shapes("CheckBox1").Visible = False
    Else
    Rows(15).EntireRow.Hidden = False
    ActiveSheet.Shapes("CheckBox1").Visible = True
    End If
    End Sub[/code]
  • JetSetWilly 25 Jun 2007 17:17:37 5,720 posts
    Seen 1 year ago
    Registered 16 years ago
    Razz, I'm not sure but isn't the equivalence operator == ? So, it should be:

    If Rows(15).EntireRow.Hidden == True Then
    ActiveSheet.Shapes("CheckBox1").Visible = False

    Edited by JetSetWilly at 17:18:25 25-06-2007
  • Tweakmonkey 25 Jun 2007 17:55:57 1,300 posts
    Seen 1 year ago
    Registered 18 years ago
    ^^^No
  • MrWorf 25 Jun 2007 19:15:19 64,172 posts
    Seen 3 days ago
    Registered 20 years ago
    Yeh, the double equals thingy didn't work.

    But I've pretty much figured it out now, instead of hiding rows 15-18 I'm hiding 15-16 whilst keeping the check box visible and being the only visible thing. The check boxes, there are 20, are under a column entitled "Relevant". So in effect it just partially "buries" the rows which aren't relevant to that particular form.

    Hope that makes sense!

    Cheers everyone for your help!

    My boss has asked me to put together this db in Excel, the c**t. >:( I could've done it easily in Access but he said we're not allowed to use it anymore. o_O Though I'm quite proficient in Excel, the more advanced conditional and VB formatting is pretty much above my level. I'll, no doubt, have more queries tomorrow. Hope I can pick your brains again!

    Cheers!
  • Micro_Explosion 25 Jun 2007 19:16:44 9,692 posts
    Seen 12 years ago
    Registered 15 years ago
    Why won't the fool let you use Access anymore?
  • pauleyc 25 Jun 2007 19:26:10 4,548 posts
    Seen 5 hours ago
    Registered 20 years ago
    Excel instead of Access? Sheesh! I have a colleague who specializes in such atrocities. His biggest "invention" weighs over 40 MB and changing every minor detail forces a recalculation that takes about 20 seconds.

    I can do better and faster things in Lotus Approach but he's a pal of our GM...

    Aaaanyway, Razz - to increase the confusion, you could also switch predefined forms using drop-down lists, that's even more of a headache. ;-)
  • Micro_Explosion 25 Jun 2007 19:29:42 9,692 posts
    Seen 12 years ago
    Registered 15 years ago
    /secretly has 80+meg excel files
    /and files with calculations that takes 5 minutes each time
  • MrWorf 25 Jun 2007 19:35:15 64,172 posts
    Seen 3 days ago
    Registered 20 years ago
    o_O

    That's sick! :D

    I have to use Excel or we have to submit a comprehensive business plan as to why we need to use Access. My boss is not prepared to do that. Couple that with the fact that the execs don't people using access any more make any hope for using access futile. :/

    Ah well, I guess I'm learning some new fings. :)

    w00t. :(

    Oh nice idea about pre-defined forms but there's a reason why I that won't work for me that I remembered earlier that escapes me now.... erm... cheers though!
  • MrWorf 26 Jun 2007 12:13:53 64,172 posts
    Seen 3 days ago
    Registered 20 years ago
    Riiiiiiiiiiiiiiiight.

    I've done away with the VB stuff and have decided to go with macros. It was going well but I've hit a slight snag. I'm trying to create a macro to hide rows for a Risk Assessment spreadsheet. When I click the checkboxes the rows I want to be hidden are hidden without fault. However, the checkboxes remain on the spreadsheet.

    Is there a code to hide checkboxes once hidden? I tried guessing the code trying similar scripts to the VB code from yesterday to no avail. :/

    In addition, I'm using a Commandbox to recall (i.e. Show All) the the hidden rows. Please find my current code below.

    [code]------------------------
    'Below are the checkboxes
    Sub CheckBox13_Click()

    ScreenUpdating = True

    For Each r In Range("D15:D18")

    If r.Value = 0 Then

    r.EntireRow.Hidden = True

    ElseIf r.Value = 1 Then

    r.EntireRow.Unhidden = True

    End If


    Next

    End Sub

    Sub CheckBox17_Click()
    For Each r In Range("D19:D22")

    If r.Value = 0 Then

    r.EntireRow.Hidden = True

    ElseIf r.Value = 1 Then

    r.EntireRow.Unhidden = True

    End If


    Next
    End Sub

    'Below is the button I use to unhide all the hidden rows
    Sub Button15_Click()

    For Each r In Range("D1:D122")


    If r.Value = 0 Then

    r.EntireRow.Hidden = False

    End If

    Next

    End Sub
    --------------------[/code]

    Please help! ;_;

    In a nutshell I need to be able to hide the checkboxes once they've been clicked and then unhide them, along with the rows, when I click the command button.

    Many thanks in advance!

    -Razz
  • MrWorf 27 Jun 2007 15:53:51 64,172 posts
    Seen 3 days ago
    Registered 20 years ago
    Sorted it! :)

    I went back to using the original code that paulyc suggested. The checkboxes now disapear. Seems all I needed to do was select " Move and Size with cells" under properties of the format control context menu! Doh! Silly mistake on my part. :))

    Urm... does anyone know how I can program a Command button to copy a worksheet to a new worksheet?

    Thus far I've succeded in programming a button to create a new worksheet at the end of the sheet list. However, it won't carry over the contents of the worksheet that I clicked the button in.

    Also, is there a way the button could have a self destruct command so it doesn't appear in the new sheet?

    Sorry if this is basic stuff I'm new to VB.

    Edited by Razz at 15:55:27 27-06-2007
  • Grunk 27 Jun 2007 15:55:50 4,717 posts
    Seen 7 years ago
    Registered 16 years ago
    Just record a macro to do it:

    press record
    cut and paste
    press stop

    then assign it to a shortcut key

    if you want it to do anything else you can add it into the macro code (VB) it created using the editor

    Edited by Grunk at 15:56:17 27-06-2007
  • MrWorf 27 Jun 2007 16:25:19 64,172 posts
    Seen 3 days ago
    Registered 20 years ago
    Oh cheers!

    Tried that, but it doesn't retain the cell sizes.

    So instead of copy pasting I tried right clicking the sheet and copying it. It's worketh! So I bonded the code to a button.

    However, it will only copy the worksheet to a position after sheet 6, i.e.

    [code]Sheets("BLANK").Copy After:=Sheets(6)[/code]

    I need it to copy the worksheet to position after whatever the last worksheet will be as the "BLANK" sheet is a template and the copy will be a completed form.

    I've tried various things, like changing the "6" to "last" or a * but for the life of me I can't figure it out! ;_;

    Any ideas?

    Edited by Razz at 17:10:17 27-06-2007
  • First Previous
Sign in or register to reply

Sometimes posts may contain links to online retail stores. If you click on one and make a purchase we may receive a small commission. For more information, go here.