Trouble shooting a user form code

[JO505]

I have tried to create a user form by adapting a U Tube lesson on user forms. So far the attached is what I have come up with but I cant get it to work. I think most of it is straight forward with some exceptions. I should also note that when it comes to VBA I know very little, just enough to get into trouble.

The data should paste to the log "Job" . Column "A" is blank, column "B" contains a ormula copied from the cell above the current entry, columns "C " thru "G" are populated from the form. Their are then a range of formulas that will be copied from the previous row, I just need to know how to code them. Can I just have a line of code that will enter the formulas as a range or will I need to write a line of code for each column?

Please Login or Register  to view this content.

Jim O

[scott.s.fower]

eRow = Job.Cells(Row.Count, 1).End(x1Up).Offset(1.0)Row

the offset portion of the above code should be Offset(1,0) you have a period instead of a comma. I didn't test the actual code so I'm not sure if there is something else that needs to be done

you can go to http://www.fontstuff.com/ebooks/free/fsUserForms.pdf to get more info on forms, it's a pretty good resource

you might also have to define the source ie. cells(eRow, 2).value = Form1.eRow - 1 (where Form1 is the name of your form)

[JO505]

Scott,

I am still having problems. I'm getting a Syntax Error now.

Here is where I am at so far.

Please Login or Register  to view this content.

Jim O

[JO505]

Any thoughts out there?

Jim O

[JO505]

I think we are making some progress, at least I am going to call it that. I now get a "Compile Error", "End If without Block If". I have no idea what it means but it is different than any other error I have gotten. Below is the current code.

Please Login or Register  to view this content.

Jim O

[JO505]

I still haven't been able to find out what "End If without Block If" is about. All I know is it is stopping the code at this point.

Any help!

Jim O

[JO505]

I still haven't been able to find out what "End If without Block If" is about. All I know is it is stopping the code at this point.

Any help!

Jim O

[JO505]

I took out the "End If" line and I can now add most of the values to the database. I am still having an issue with copying the formula from the preceeding row (cell) and pasting that into the approprate cell and with formating of some data, currency for example.

Column "B" (2) contains a formula that needs to be copyed down and column "G" (7) needs to be formated as a dollar amount.

Jim O

[JO505]

I have tried to format the columns to the appropraite style but when I run the code it does not show up. A column that is formatted as $0.00 should show $5.00 if a 5 is entered in the combo box on the form. When I run the code it shows up as a 5 . Where do I go from here?

Jim O

[jaslake]

Hi Jim O

Helping you trouble shoot will be much more productive if you will attach a copy of the File WITH existing Code.

[JO505]

John,

Thanks for your reply.

The code seems to work OK (though it does run slow) with the exception of some formating.

I have attached the current code. In particular, Row "G", (iRow 7). If a number is entered in the combo box then I need to return dollars and cents, if a word is selected from the combo box I need that entered.

As this is my first real attempt at code I am totally open to any suggestion anyone may have. I have made some minor modifications in other code such as cell target changes etc. but this one I have done from scratch. Further as their are so many instances of copying the formula from the previous entry, if their is a better way to accomplish this I am all for it.

Thanks
Jim O

[jaslake]

Hi Jim O

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" worksheet that demonstrates what you wish the output to be.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the "Add Files"... button to locate your file for uploading.
6. This will open a new window File Upload...Click "Select Files"
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the "Upload Files" button and wait until the file has uploaded.
10. Click the "Done" Button.

[JO505]

Here is the file.

I am working with the "Add New Job" form. It posts to the sheet Job but I can't get the formatting in colunm "G" to work.

When I get it resolved I will deal with the other form in another post.

Jim O

[jaslake]

Hi Jim O

Add this Code to UserForm2

Please Login or Register  to view this content.

Then change this line of Code

Please Login or Register  to view this content.

Works for me...how about you?

[JO505]

John,

That appears to do the trick.

Is their any way to speed up the process or do I just have to learn to live with it? Its not really a big deal but it seems to slow everything down if that file is open.

Thanks so much for your time with this.

Jim O

[jaslake]

Hi Jim O

No

do I just have to learn to live with it

You have Workbook Calculations going on up the Wazoo...turn them off...turn them back on when you're done...it's what I did in testing. Need help with this (after you've done a bit of research) let me know.

[jaslake]

Hi Jim 0

Here's an example

Please Login or Register  to view this content.

[JO505]

I am not fami;iar with the term "workbook calculation". Is that just calculations that are going on as a result of having formulas doing their thing?


I assume you just add that new code in the code before and after the current code?

Jim O

[jaslake]

Hi Jim 0

Essentially this is correct

Is that just calculations that are going on as a result of having formulas doing their thing

When you press the CmdAdd_Click Button it's doing a recalculation of all dependent Cells that are modified. You have many Formulas and Dependencies in your workbook and they're all being recalculated as you modify Cells.

You need to manage that recalculation or you'll never be happy with the performance of your project.

Essentially this is also correct

I assume you just add that new code in the code before and after the current code

Except you need to do a recalculation AFTER the Code has executed...else your results will be incorrect. The good thing is you do the recalc only once...at the end of the Code...not as each Cell changes.

[JO505]

John,

That is sweet! It works like a charm. Thank you very much.

Jim O

[jaslake]

You're welcome...glad I could help. Thanks for the Rep.