As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/
Visual Basic User Form Problem
crucialityfactor
Registered User regular
Registered User regular
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.
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
0
Posts
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).
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.
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).
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.
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
Thanks for your help so far guys. I hope this will help to clear up your questions.
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.
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.