Our new Indie Games subforum is now open for business in G&T. Go and check it out, you might land a code for a free game. If you're developing an indie game and want to post about it, follow these directions. If you don't, he'll break your legs! Hahaha! Seriously though.
Our rules have been updated and given their own forum. Go and look at them! They are nice, and there may be new ones that you didn't know about! Hooray for rules! Hooray for The System! Hooray for Conforming!

Visual Basic User Form Problem

crucialityfactorcrucialityfactor Registered User regular
edited April 2008 in Help / Advice Forum
Hello All,

I am trying to create a user form using Visual Basic in Excel 2003.

This is my first time programming in VB and I've been following instructions from Here as a basic guide.

Here's what I want to do:

The Excel document is either emailed to an applicant, or they access from a public directory. When they open up the file, there is just a button that says something like "Click here for the Pathways to Excellence Application". When they click on this button a Macro then runs to open the form. The form pops up, the user inputs their information, then they hit the Enter button and all the information is put into the spreadsheet in an organized manner. The user then saves the file and emails it to the employee who handles the applications.

What I have so far is just a basic 1st draft of the code. It's not organized too well, I just basically have all the text boxes, drop downs, and check boxes defined. I also have a rough input setup where all the different variables get put into the spreadsheet. I have a button on the spreadsheet and a Macro on it (that I THINK works) I think there's just something wrong somewhere in the code that is preventing it from showing and running the way I hope it will.

My level of knowledge/comprehension of this language is that of a five year old who just learned how to read and write a few basic sentences. I'm sure there are a lot of better ways for me to have things defined and laid out. Right now, I'm not really concerned with making a pretty looking code. I just want it to run the way I want it to for right now. I'm taking a VB class this summer, so I'll learn all that advanced stuff then. No one who I work with will look at this code and probably only 1 or 2 other people will understand it. My boss isn't going to say "this code isn't pretty looking" My IT department is usually just concerned with maintaining our network and making sure all the users PCs, printers, phones and programs work.

This is the link for the actual file I'm working on

This is the link for how I want the data to be stored on the spreadsheet


Any help you can give me would be greatly appreciated, my job isn't on the line with this...I'm just looking for brownie points for doing something new.

crucialityfactor on

Posts

  • JasconiusJasconius sword criminal Flo-ridaRegistered User regular
    edited April 2008
    Holy crap dude, I can't see exactly what your problem is but you need to learn about subs to not make that code ridiculously long.

    All that .additem crap can be synced into one simple sub routine and make your code half as long or less.

    Example:

    Sub name(object as Whatever)

    With object

    .additem crap goes here

    End With

    End Sub

    Just make sure you are passing the object by reference and not by value. VB intellisense has a nasty habit of defaulting to ByVal (which is fucking dumb, thanks a lot VB).

    My company is looking for a remote short term (3-6 mo) contractor with remarkable JS and CSS experience who has worked in a team environment at least once before. If you are or know someone who is, PM me for details.
  • crucialityfactorcrucialityfactor Registered User regular
    edited April 2008
    Yeah, I know that it gets lengthy in those sections. I really don't know much about VB so I was just going on instructions for a really basic form that didn't have to deal with a lot of long lists.

  • TyrantCowTyrantCow Registered User regular
    edited April 2008
    Are you using notepad and coding VB?
    Or using VIsual Studio or something... what's going on there... that's madness.

    What is this Raptor?!

    edit:
    What version of VB?

    Can you explain more about what you want the end result to be?

  • crucialityfactorcrucialityfactor Registered User regular
    edited April 2008
    TyrantCow wrote: »
    Are you using notepad and coding VB?
    Or using VIsual Studio or something... what's going on there... that's madness.

    What is this Raptor?!

    edit:
    What version of VB?

    Can you explain more about what you want the end result to be?

    I'm using the Visual Basic through Excel 2003 with the latest Service Packs.

    Basically the whole point of this is for someone to click on a button on the spreadsheet, which opens up the form. The Employee then fills out the form. When they hit the "enter" button all the information gets placed into the spreadsheet, the user then emails the file to the Human Resources person who deals with applications.

  • VThornheartVThornheart Registered User regular
    edited April 2008
    CrucialityFactor, I don't see the form attempt to make itself visible (either with a Me.show or Me.visible = true method). Is this form being loaded by another form or some other code? If so, make sure that *something* sets the form to visible (be it the code that calls this form, or a method in the form itself that gets called).

    If you don't set it visible through .Show(), .ShowDialog(), or just plain ol' .visible = True, then if it's not set as the initial form in the program you won't ever see it.

    EDIT: Ack, I just noticed you said it was through Excel. In that case it's VBA, which I haven't worked with. I'm not sure if you have to explicitly tell the window to be shown or make itself visible with VBA, but I at least imagine that you would. Check on this in your code if possible and see if you can. Do you attempt to call this form from other code? If so, post it here. If not, explain how it is supposed to get loaded if you could. I have extensive VB experience, but none with VBA so this may be an aspect of VBA with which I'd be unfamiliar (if it works differently than VB).

    3DS Friend Code: 1950-8938-9095
  • VThornheartVThornheart Registered User regular
    edited April 2008
    Jasconius wrote: »
    Just make sure you are passing the object by reference and not by value. VB intellisense has a nasty habit of defaulting to ByVal (which is fucking dumb, thanks a lot VB).

    Aye, they thought it'd be a good idea to have it pass By Value as default because the way VB stores objects, it's just as quick as a by reference pass... and in truth, from what I hear they thought that their target audience might not understand the concept of passing by reference from the onset. But even given it's limitations, I'm a fan of the language. =) But I digress heavily... anyways, crucialityfactor if you can answer the questions in the post above we should be able to help more.

    3DS Friend Code: 1950-8938-9095
  • crucialityfactorcrucialityfactor Registered User regular
    edited April 2008
    Sorry, I should have included that.

    From what I understand, you just make a Macro to run the form by clicking a button on the spreadsheet. I'm going to update the OP cause I feel I was really vague with everything. Here's the code for the macro:

    Sub OpenPathwaysToExcellenceForm()
    frmPathwaysToExcellence.Show
    End Sub

  • VThornheartVThornheart Registered User regular
    edited April 2008
    Ah, so you did call Show. Hmm... I was thinking maybe you forgot that part. =) Lemme take a look.

    3DS Friend Code: 1950-8938-9095
  • crucialityfactorcrucialityfactor Registered User regular
    edited April 2008
    OP Updated.

    Thanks for your help so far guys. I hope this will help to clear up your questions.

  • VThornheartVThornheart Registered User regular
    edited April 2008
    In your Initialize event handler, you have:

    With cboToTimeThusday

    See that cboToTmeThusday? It's a typo, you'll want to correct that in order for it to work correctly. That may be what's causing the problem.

    EDIT: Aye, it loaded properly after I changed cboToTmeThusday to cboToTimeThursday, which was an actual object that existed in the form.

    I'm surprised that it didn't bring up an error message for you. Did you somehow disable debugging? This could be helpful for you... open up the Visual Basic editor in Excel, and look at the code window for frmPathwaysToExcellence. You'll notice a gray bar running down the length of the left side of the window. If you left click in there, a red dot will appear. From then on (until you left-click the red dot a second time), the code will stop executing at that dot and you'll be able to run through line-by-line from there. This is known as a breakpoint, and it'll prove extremely useful while debugging. (You may already know this, I apologize in advance if you do... just making sure you know, because it's super important to know about). Set a red dot at the start of your Initialize function, and then press the button in Excel. You'll see a yellow box appear in the code window, and when you press F8 it will bring you to the next line. If you keep doing this, you should see where your errors occur if there's any more to be found.

    You can set multiple of these dots, and it will prove infinitely helpful in your quest to find what's going wrong (if anything else ends up going wrong).

    Hopefully this is helpful.

    3DS Friend Code: 1950-8938-9095
  • crucialityfactorcrucialityfactor Registered User regular
    edited April 2008
    In your Initialize event handler, you have:

    cboFromTimeThursday.Value = "N/A"
    With cboToTimeThusday


    See that cboToTmeThusday? It's a typo, you'll want to correct that in order for it to work correctly. That may be what's causing the problem.

    Oh shit, thanks.

    That was it...I hate stupid typos like that. I've been looking over this code all week, I'm glad that a fresh pair of eyes can catch that.

  • VThornheartVThornheart Registered User regular
    edited April 2008
    In your Initialize event handler, you have:

    cboFromTimeThursday.Value = "N/A"
    With cboToTimeThusday


    See that cboToTmeThusday? It's a typo, you'll want to correct that in order for it to work correctly. That may be what's causing the problem.

    Oh shit, thanks.

    That was it...I hate stupid typos like that. I've been looking over this code all week, I'm glad that a fresh pair of eyes can catch that.

    Aye, in truth I (and most programmers) do it all the time. These kinds of small typos are among the reasons why it's becoming more popular for people to program in groups of two. However, some IDEs will help you out with this (i.e. the new Visual Studio environments) so you don't really need help with typos for the most part... but things based on Visual Studio 6 (as the Microsoft Visual Basic for Applications editor in Excel is) don't have that kind of "oops, you made a typo -> Here" kind of thing, which sucks.

    But you should've at least seen an error message about it when you ran it. I don't see any settings for disabling these messages, so I don't know why you didn't see it. Very odd. =( But if you use that debugging trick (breakpoints) that I mention above, it should prove quite helpful if things like this happen in the future.

    3DS Friend Code: 1950-8938-9095
  • crucialityfactorcrucialityfactor Registered User regular
    edited April 2008
    In your Initialize event handler, you have:

    cboFromTimeThursday.Value = "N/A"
    With cboToTimeThusday


    See that cboToTmeThusday? It's a typo, you'll want to correct that in order for it to work correctly. That may be what's causing the problem.

    Oh shit, thanks.

    That was it...I hate stupid typos like that. I've been looking over this code all week, I'm glad that a fresh pair of eyes can catch that.

    Aye, in truth I (and most programmers) do it all the time. These kinds of small typos are among the reasons why it's becoming more popular for people to program in groups of two. However, some IDEs will help you out with this (i.e. the new Visual Studio environments) so you don't really need help with typos for the most part... but things based on Visual Studio 6 (as the Microsoft Visual Basic for Applications editor in Excel is) don't have that kind of "oops, you made a typo -> Here" kind of thing, which sucks.

    But you should've at least seen an error message about it when you ran it. I don't see any settings for disabling these messages, so I don't know why you didn't see it. Very odd. =( But if you use that debugging trick (breakpoints) that I mention above, it should prove quite helpful if things like this happen in the future.

    Yeah, I got an error message that basically just said "Error '424': Object not found" and when I clicked on debug it pointed to the Show line in the macro. So, I had no idea what that meant cause I was sure that that line was at least right. Earlier today I found a bunch of typos where I added periods in the wrong places, and I didn't catch anymore so I was thinking that maybe I was missing something where I'd have to define the macro to allow it to call the form or something crazy like that.

  • VThornheartVThornheart Registered User regular
    edited April 2008
    Aye, if you don't step through with breakpoints, that old VB6-style debugger won't tell you which line it is exactly. It'll generally tell you a level above where it actually happened. Now, if you step through it line by line when that happens, you'll definitely hit on it.

    3DS Friend Code: 1950-8938-9095
Sign In or Register to comment.