macro - to read tick box

[Laughingman]

Hi Guys,

Run into a bit of a problem - I normally use Access for this kind of stuff but this time I have to use Excel, I have a simple workbook with 3 sheets to it, the first is going to be a data entry form and the 2nd and 3rd sheets are to store details of communications inbound and outbound (1 sheet for inbound the other for outbound) both datasheets have the same data fields. The front page form has a button that will update the sheets and a couple of tick boxes to denote weather the data is for the inbound or outbound data sheet. - piece of cake in access - excel isn't playing ball and I can't get a macro to read the tick boxes and apply the data to the correct sheet so far it's either been going to both or neither.... any Ideas

 

[bertie]

Hi and welcome to the board.

Consider whether you are using the correct control.

Option (or Radio) buttons are used when you want to give the user an Either/Or choice. i.e., Process one thing OR Process another, not both.

Tick/Check boxes are used when you want to give the use and AND choice. i.e., Process this AND process that.

Option/Radio buttons can be set to be mutually exclusive:
Right click, select Properties and assign a GroupName.
When you select one option button all other buttons within the group are deselcted.

The option button returns a value True/False depending on if it has been selected/deselected. We can use this in code:

   [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]True[/COLOR]
      [COLOR=darkblue]Case[/COLOR] optSheet2
         sheetName = "Sheet2"
      [COLOR=darkblue]Case[/COLOR] optSheet3
         sheetName = "Sheet3"
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
   
   [COLOR=green]'code to transfer data[/COLOR]

Where optSheet2 and optSheet3 are names assigned via the controls Properties windows.

Tick/Check boxes can be coded to behave in this mutually exclusive manner but it would take a lot more code.

Hope this helps.
Bertie

 

[Laughingman]

Hi Bertie,

Thanks, I think that would do it - the reason I was using tick boxes is it gets printed out as a paper form for people to fill out then those forms get dropped off with a colegue. She has the fun job of putting it all on the workbook and I have found it's easier to make both electronic and paper forms to look identical else chaos reigns - we shall have to see how it goes.