New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Need Help W/ Code for Work Project

Macros, VBA, Excel Automation, etc.

Re: Need Help W/ Code for Work Project

Postby laurensims41 » Fri Feb 05, 2016 4:37 pm

ccchapman: I am not opposed to anything i would love to take a look. Thanks for taking a look.
  • 0

laurensims41
Rookie
 
Posts: 15
Joined: Feb 3, 2016
Reputation: 0
Excel Version: Office Professional Plust 2010

Re: Need Help W/ Code for Work Project

Postby NoSparks » Fri Feb 05, 2016 5:04 pm

Hello Chris, welcome to the forum and thanks for joining in.
This is a small forum with nowhere near the traffic of the big guys and as such seems some what friendlier, but definitely needs all the support it can get.
I'm sure any and all assistance you offer will be much appreciated.


Lauren,
I had trouble dealing with the option buttons so resorted to combo box drop downs.
This should work as long as what the drop downs are populated with match the sheet names exactly.
You can see how that's done in the code for initializing the user form.

Hope this is of some assistance. Any questions, just ask.

Good luck with the project.
  • 0

You do not have the required permissions to view the files attached to this post.
NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: Need Help W/ Code for Work Project

Postby laurensims41 » Fri Feb 05, 2016 6:01 pm

I can't thank you enough for helping me with this. I have been starring at it for three days and have started over 1,000 times. The good news it got me back familiar with some of the processes of visual basic. User form is great and it works great.

I think I was making this more difficult then it needed it to be.

Would you mind explaining to me what part of the code ties in the choices with the correct sheet. Just for my knowledge.
  • 0

laurensims41
Rookie
 
Posts: 15
Joined: Feb 3, 2016
Reputation: 0
Excel Version: Office Professional Plust 2010

Re: Need Help W/ Code for Work Project

Postby NoSparks » Fri Feb 05, 2016 6:51 pm

Don't do cartwheels yet, let's see what Chris suggests.
Depending on the rest of the project, dealing with things right on the sheet may be simplest as there's way more than what meets the eye that can go goofy with a user form, they have the potential of requiring more maintenance.
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: Need Help W/ Code for Work Project

Postby NoSparks » Fri Feb 05, 2016 7:38 pm

what part of the code ties in the choices with the correct sheet

Have a look in the Sub cmdWrite_Click() procedure.
Using 4 variables, ws1 thru ws3 are given values equal to what's in the selected drop downs. Each of these is an exact sheet name (providing the user form initialization was right).
ws4 is a little different because there's only one sheet here so it either gets the name or it doesn't.

We then use For i = 1 to Sheets.Count to look at every sheet in the workbook one at a time.
If the sheet name is equal to any of ws1 thru ws4, everything on that sheet is copied and written to the Finished sheet's next blank line.

Hope that helps.
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: Need Help W/ Code for Work Project

Postby NoSparks » Fri Mar 04, 2016 12:11 am

Lauren, please, don't PM requests, post them to the forum.
By all means PM to ask that the new request be looked at but the request should be open to all.

The request is that the current sheet being assembled via user form be saved to the user's desktop as a text file.

I've added a procedure in module 1 and call it at the end of the user form "Write data to Finished Sheet" button's procedure.

Hope this does what's required.
  • 0

You do not have the required permissions to view the files attached to this post.
NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Re: Need Help W/ Code for Work Project

Postby pecoflyer » Fri Mar 04, 2016 10:52 am

NoSparks wrote:This is a small forum with nowhere near the traffic of the big guys and as such seems some what friendlier, but definitely needs all the support it can get.



Thank you :lol:
  • 0

A relevant topic title helps get faster and more answers
pecoflyer
Moderator
 
Posts: 1274
Joined: Jan 24, 2012
Location: Belgium
Reputation: 39
Excel Version: 2003/2007/2010

Re: Need Help W/ Code for Work Project

Postby laurensims41 » Sat Mar 05, 2016 4:07 pm

NoSparks, thanks again. I apologize for PMing you I am unaware of the forum rules.

This works exactly how I wanted it to. my code was replacing the name of my sheet and then saving it.

I have a few questions for my own notes. What does the Sub Check of characters do?
and was the point of the loop in the create text file. (does it loop through and write every line of my sheet to a text file?)

Thanks again so much.
  • 0

laurensims41
Rookie
 
Posts: 15
Joined: Feb 3, 2016
Reputation: 0
Excel Version: Office Professional Plust 2010

Re: Need Help W/ Code for Work Project

Postby NoSparks » Sat Mar 05, 2016 7:32 pm

What does the Sub Check of characters do?

Hmmm.... rather observant of you. ;)
It does nothing. Well, it does nothing for what you're doing. I just forgot to remove it.

It's a little procedure I use to find out what's really in a cell. It prints every single character to the debuggers Immediate Window, both the ascii character number and what Excel displays for that number. Lets me see if there are invisible characters needing to be dealt with.

the point of the loop in the create text file. (does it loop through and write every line of my sheet to a text file?)

yes, the A cell and the B cell are joined together with a space between and then written to the text file. The loop deals with one row at a time and creates one text line for each row.

Hope that helps
NoSparks
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010

Previous

Return to Macros and VBA Questions

Who is online

Users browsing this forum: Google [Bot] and 38 guests