Checkboxes 1

[N1k]

Hello all

Hope you can help! I have spent weeks trying to learn VBA on my own and think I have almost read every page on the net, my head aches. I am stuck! I have the form created and think I have loaded it properly, now I need some check boxes to work, can you tell me if I am coding it right for what I want to do. I am trying to activate checkboxes.Say I have 5 check boxes, if the user ticks check box 1, I want it to go on my worksheet in column G2, if checkbox 2 is ticked then H2 and so on. Now if I have followed instructions correctly on the net the first part of my code should be right, they are text boxes, its when it gets to checkbox 1 ..........

Private Sub Savebtn_Click()
ActiveWorkbook.Sheets("Sheet1").Activate
Range("A2").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Value = DateCall.Value
ActiveCell.Offset(0, 1) = CallerName.Value
ActivateCell.Offset(0, 2) = StreetName.Value
ActivateCell.Offset(0, 3) = City.Value
ActivateCell.Offset(0, 4) = PostalCode.Value
ActivateCell.Offset(0, 5) = PhoneNumber.Value

If checkbox1 = True Then

ActivateCell.Offset(0, 6).Value = "Yes"

Else

ActivateCell.Offset(0, 6).Value = ""

ElseIf

Checkbox2 = True Then
ActivateCell.Offset (0,7).Value= "Yes"
Else
ActivateCell.Offset (0,7).Value - ""

Else
Checkbox3 = True Then
ActivateCell.Offset (0,8).Value = "Yes"


and so on

End If


The problem I have when I choose RunSub Userform from design and I have to use Task Manager to close it all down I will sort later! My life is dependent on this form :-(

 

[Loomah]

hi
first things first - keep trying! it takes a little more than 'weeks' to grasp vba but you seem to be well on the way! the key aspect of vba that is often overlooked is that knowledge of your host app is as important as programming know how!

now some thoughts
first of all i'd recommend using a different method to find your first empty cell - using myrange=cells(rows.count,1).end(xlup).offset(1,0) would give you something close then you can use "myrange.offset(0,1)=" etc

secondly it's very rarely you need to select anything in vba

thirdly, are you checkbox selections intended to be mutually exclusive? what is supposed to happen if 2 are selecte?

finally why are you needing to use task manager to kill the app? does the query close button (the little x in the top right) not work? if not what happens when you hit ctrl+break? it should terminate the code and allow you to debug to see where the problem lies?

if it's a cse of simply hiding the form when you click your button add the code me.unload at the end.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

Hi,

Did you do a COMPILE? You have syntax errors on your ElseIf statements!!!

[s]
ElseIf

    Checkbox2 = True Then
 [/s]

rather

ElseIf Checkbox2 = True Then

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

1. totally agree with Loomah, keeo at it! Also with:

the key aspect of vba that is often overlooked is that knowledge of your host app is as important as programming know how!

There is nothing, IMO, that beats knowing WHAT the host apps does (or is capable of doing); or in other words, knowing the Object Model.

2. are you using Option Explicit? Skips point about the syntax error is correct, althogh your post could that way because of how you typed in in. BTW: please use the code tags when posting code. If you do not know how, please click the "Process TGML" link at the bottom of the response window.

3. back to Loomah, I think it is important that you work out the complete logic.

thirdly, are you checkbox selections intended to be mutually exclusive? what is supposed to happen if 2 are selecte?

Logically speaking, if they are to be mutally exclusive, this is utterly different from a situation where they are NOT mutually exclusive. (HINT: if they ARE mutually exclusive, it would be better to use optionbuttons in a Frame.)

4. I too am seriously wondering about why you say you have to use Task Manager. Seems odd.

5. Please confirm that these are checkboxes on a userform. It looks like they are, but I want to be sure.

Gerry

 

[N1k]

Hello guys, thanks for taking the time to reply. Learning the 'Host App' I need to go find out what that is. The thing is, I need this project completed so jumped steps and don't understand what I am doing which is a huge problem I know. I have today placed an advert locally for a Tutor. None of the colleges nearby teach this specifically. I find it all very interesting so want to learn.

So... myrange=cells(rows.count,1).end(xlup).offset(1,0) would give you something close then you can use "myrange.offset(0,1)=" etc Ok I will try this out tonight, once I feed the kids!

The checkboxes, there are 3 lots of approx 14. I did think of the option button but that would only allow 1 item to be checked and I need to allow more than that to be checked. What I did was created a column on the spreadsheet for each check box and hoping that once a check box is ticked the spreadsheet will end up with a tick/note of some sort in the corresponding column.

My task Manager problem! Well to be honest I have no idea, it didn't happen at the start so perhaps I have corrupted the document in some way but looks like I may have to start all over again once I have learned how to do it :-( The minute I hit Run Sub userform it opens but wont allow me to close it, I can save it, but then have to use task manager.

I have thought about using the services of one of the companies on the internet to complete this project but apart from the cost, I really want to learn and understand.

Will also look at TGML, sorry!

I saw a tutorial and certification on VBExpress, do you have any ideas if that is any good?

Thanks

 

[fumei]

1. "The checkboxes, there are 3 lots of approx 14. I did think of the option button but that would only allow 1 item to be checked and I need to allow more than that to be checked. "

Then they are NOT mutually exclusive.

2. "What I did was created a column on the spreadsheet for each check box"

Then thery are NOT on a userform.

3. "The minute I hit Run Sub userform it opens but wont allow me to close it, I can save it, but then have to use task manager."

Then there IS a userform...I think.

WHAT opens????? If it is a userform, unless you are doing something like an infinite loop, you can always close it. You do not save userforms, you save files.

So, frankly, I am confused as to what is happening.



Gerry

 

[N1k]

Sorry Gerry, think my brain is just completely fried now and I obviously havent explained very well. Yes it is definitely a Userform.

When I open the spreadsheet, I then open the userform. From here if I choose Run sub userform it opens the userform. Only it wont close again! I have no idea if it's an infinate loop or not, probably not since I dont know what that is, I just know that it really hates me and wont close.

When I say I have created columns on the spreadsheet, this is what I assumed I would have to do to allow the information collected from the userform to be placed on the spreadsheet. Have I got this completely wrong - I just want to cry now! But I wont I will keep learning.

 

[Loomah]

ok, lets take a little step back from this!

first to explain - the host app is whichever program you are using to 'host' your code. in your case it's excel but could as easily be access, outlook, powerpoint or word. And it doesn't hate you. i know this because you fed the kids!

it does seem like there is an infinite loop.
this is where a loop (in this case a do...loop) is initiated but the criteria for ending it (Loop Until IsEmpty(ActiveCell) = True) is never met. in you case this seems highly unlikely looking at the code but not impossible.

try running the form then while it appears 'stuck' hit ctrl+break together. if you get a dialog saying execution has been interrupted (or similar) click debug and see what it highlighted.

post back with your findings...!

but the best thing to do is to eliminate this loop by using my earlier suggestions!

fianlly(!) what happens after

and so on

??

is it just the remainder of the if checkbox statements?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[fumei]

One way of testing things with a userform is to not simply execute it (with Run, or F5, in the VBE), but to Step through it.

When you Step through any procedure (code), you can ALWAYS stop it by clicking...the Stop button.

When I open the spreadsheet, I then open the userform. From here if I choose Run sub userform it opens the userform.

This is mutally exclusive.

Try reading that:

I then open the userform...from here I choose Run Sub userform it opens the userform.

How can that be possible? You open the userform, and then open it again?

Sorry, this is not possible. The userform is either open - actually .Show - or it is not.

Gerry

 

[N1k]

Hi, Where I am at. I started over and have created my first basic coded form complete with txt cmd and chk boxes. I have just a little tweeking I want to do but want to try figure it out. I am so pleased with my little self but now got hooked and need to learn more.

I did however keep the first one because I can learn from mistakes. I went back to the Run, sub user and I still need to use Task Mgr to close it because I cannot do anything else. I tried the Ctrl+Break and the debug but none of these made any difference. The only loop was the one posted in my initial post. I think it is very very corrupt!

Thanks for the help but I may be back! I am off to collect my VBA book from the Book store

 

[N1k]

Sorry, forgot to say - The debug came back with nothing, no highlights, no dialogs to say there was an error and I know that this would normally happen because it did all that when I was doing the second one which really helped me along!

And, yes I fed the kids but they are now telling me stories about moms who are obsessed with their computer and ignore the fact that they have kids who have needs! Hmmm guess I am being told something eh!

Thanks guys for the info