VBA Help!

[Calvinyeo1993]

Hi guys, I need help in coming out with a formula/macro, when I punched the command button, with the criteria of the check boxes being tick (eg, Comms & Store), it will link to C&C+Store worksheet in the same excel file. Help is much appreciated, I had tried running Macro but it didn't work! Thank you!

 

[Calvinyeo1993]

[/IMG]


Sorry this is the problem i'm facing! Thanks once again.

 

[jproffer]

It looks like you tried to post a picture, but you can't until you have a certain number of posts...not sure what the number is.

You can, however, post your code...that would be a good start. Wrap it in code tags like this [ code ]your code here [ /code ] but with no spaces in the tags.

it should look like this when you do it right.

your code here

EDIT: your picture is up now

 

[cerfani]

This code is incomplete and needs to be edited a little so it builds the correct sheet names but the basic idea would be to check each checkbox and modify the sheet name appropriately...

Sub SelectSheet()
    Dim sheetName As String
    If checkbox1.Checked Then sheetName = Range("B6").Value
    If checkbox2.Checked Then sheetName = sheetName & Range("B7").Value
    If checkbox3.Checked Then sheetName = sheetName & Range("B8").Value
    If checkbox4.Checked Then sheetName = sheetName & Range("B9").Value

    Worksheets(sheetName).Select
End Sub

 

[jproffer]

Ok, you want to check certain checkboxes and then do what, again? "Link" to those worksheets...explain.

Also if you're in design mode, your button won't do anything.

 

[Calvinyeo1993]

Hi jproffer, referring to the image below, what I am trying to do is, if I checked the box for Comms(C&C) and SSU and pressed on the CommandButton1, the C&C+SSU worksheet will appear and hide all other worksheets. Can you help me with the coding? I have been trying to figure out for past 5 hours. I just need a basic guide on how to start!

 

[Calvinyeo1993]

[/IMG]

 

[jproffer]

Sorry it's so late. I'll see what I can come up with tomorrow at work (no excel at home). Should be pretty straight-forward coding though. Not sure of your CB names and such, but something like:

 If CB1.checked = true then Sheets("Comms").Visible = True

I dunno...the syntax might be wrong (most likely. As I said, no excel at home to test on), but it'd be something like that.

I'll see what works tomorrow if nobody else beats me to it

 

[jproffer]

This will make sheets "A", "B", and "C" visible or hidden based on checkboxes checked or not. I used activeX controls rather than form controls. This goes in a standard module. Rename sheets, buttons, etc as necessary.

Private Sub CommandButton1_Click()

    If CheckBox1.Value = True Then
        Sheets("A").Visible = True
    Else
        Sheets("A").Visible = xlHidden
    End If
    
    If CheckBox2.Value = True Then
        Sheets("B").Visible = True
    Else
        Sheets("B").Visible = xlHidden
    End If
    
    If CheckBox3.Value = True Then
        Sheets("C").Visible = True
    Else
        Sheets("C").Visible = xlHidden
    End If
    
End Sub

To add more sheets, just copy and paste all 5 lines of the If Block and increment the checkbox number and sheet name to suit you.