Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2006
    Posts
    5

    Red face Unanswered: embarrasing question

    I am very new to VB and this question is so basic, I am embarrassed, yet it holding up an important process.

    I need to activate the cell under a checkbox, but I can't use a direct reference because the box may move from one place on the worksheet to another. I like the idea of using 'Find', and I think that I am getting close, but that requires me to "Set my Object variable." I get completely lost trying to understand "Set". Can anyone break this down to something understandable for me?

    This is what I have been trying to do.

    Dim maincell As Object

    maincell = CheckBox.Value("ARDS")

  2. #2
    Join Date
    Jun 2006
    Posts
    103
    Quote Originally Posted by amwiggins
    I am very new to VB and this question is so basic, I am embarrassed, yet it holding up an important process.

    I need to activate the cell under a checkbox, but I can't use a direct reference because the box may move from one place on the worksheet to another. I like the idea of using 'Find', and I think that I am getting close, but that requires me to "Set my Object variable." I get completely lost trying to understand "Set". Can anyone break this down to something understandable for me?

    This is what I have been trying to do.

    Dim maincell As Object

    maincell = CheckBox.Value("ARDS")
    are your tring to set maincell to some object?

    then you should do

    Dim maincell As Object

    set maincell = new ThisWorkbook'(this is example, i dont know what object you want it to be)

    'only then you will try to set the property inside this object to be equal to 'something

    here is my example:

    Dim maincell As Object
    'this will make maincell = to the workbook that you are working on.
    Set maincell = New ThisWorkbook

    'here actually you will get the value from cell A1 from sheet1
    A1_Value= maincell.Worksheets("Sheet1").Range("A1").Value
    hope this helps

  3. #3
    Join Date
    Aug 2006
    Posts
    5
    Thanks for your response.

    I am creating a checklist of subsystems and applications. I am using checkboxes with the names of our major subsystem and application next to the checkboxes. When a user clicks the checkbox next to one of the main subsystems, I have coded the sheet to add cells directly below that particular subsystem, and fill the cells in with the names of associated subsystem, databases, and applications.

    My problem is in referencing the location to add the new cells. I want them directly under a specific subsystem name, but that subsystem changes location whenever more cells are added. (It drops further down the list).

    I thought I could do a find for the name of the subsystem associated with the checkbox, and then activate the cell containing the checkbos so I can finally have the cells inserted under whichever cell that turns out to be. Would this be the best way to achieve this???

    So my confusion comes in coding a find for the checkbox. I know I have to do a dim statement and a set statement, but the nature/purpose of the set statement is confusing me. I haven't been able to find any written material to make it clearer for me.

  4. #4
    Join Date
    Jun 2006
    Posts
    103
    Quote Originally Posted by amwiggins
    Thanks for your response.

    I am creating a checklist of subsystems and applications. I am using checkboxes with the names of our major subsystem and application next to the checkboxes. When a user clicks the checkbox next to one of the main subsystems, I have coded the sheet to add cells directly below that particular subsystem, and fill the cells in with the names of associated subsystem, databases, and applications.

    My problem is in referencing the location to add the new cells. I want them directly under a specific subsystem name, but that subsystem changes location whenever more cells are added. (It drops further down the list).

    I thought I could do a find for the name of the subsystem associated with the checkbox, and then activate the cell containing the checkbos so I can finally have the cells inserted under whichever cell that turns out to be. Would this be the best way to achieve this???

    So my confusion comes in coding a find for the checkbox. I know I have to do a dim statement and a set statement, but the nature/purpose of the set statement is confusing me. I haven't been able to find any written material to make it clearer for me.
    but if do that using a checkbox, how many checkboxes u need? maybe u dont know the number of subsystem u will have, it hard to decide how many checkboxes u need to declare for them.

    i suggest u try using one column for the user to enter yes/no (use validation) where yes and no store somewhere on the sheet unseen column (maybe column AA)

    then maybe u have column A to list your subsystem name (i m not sure where u got these names from, but it can be from access database)
    and then let the user to pick yes or no for column B

    after that u can use code to dectect the column last row which is not empty
    then do whatever u want to do....

  5. #5
    Join Date
    Aug 2006
    Posts
    5
    You're absolutely right! It would be impossible to declare enough checkboxes.

    So are you suggesting to have one column list each subsystem and the next column list a yes or now option for the user to check?

    I think I see. Then if they select "yes" the form expands to accomodate more cells. Then if they click no, the form contracts again. Then finding the appropriate cell to activate would not be an issue....I think. I will play around with this idea.

    Thanks!

  6. #6
    Join Date
    Jun 2006
    Posts
    103
    yes...you got the idea, i think is better u use one sheet (eg. sheet1) for the user to decide which subsystem to select, so you dont have to worry about which row to start hiding, and then you will use their inputs then decide which row or column to hide away on another sheet (eg. sheet2), so you know u will always start on row 1. to do the hide and unhide, you can just use the record marco, excel will provide u the vba code to do that.

  7. #7
    Join Date
    Aug 2006
    Posts
    5

    Thank you Mkgkoh

    Very helpful! Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •