PDA

View Full Version : Solved: Form not Uploading



Holland
03-19-2010, 08:35 AM
Hi there,
Can someone please assist me with the attached.

Basically, C3 cell has a change event to run the Upload macro but it's not working as it should. Also I used to upload from one closed or open workbook now I up load from various titles closed or open workbooks based on what I input into C3.

Fyi - Workbooks are all contained within one File Folder with various sub-folders in side. Also it worked fine until I started to use various workbooks and or various sheets.

One more thing C2 & L2 have IF Statements attached to them, can't get the Conditional formatting to work to make "#NA" font white.

Hope this is not to confusing, any help would be greatly appreciated.

Thank you very much,
Holland

p45cal
03-19-2010, 11:33 AM
See how you get on with the attached.
Files should be in the same folder as the Form Master file (not in subfolders).
There may be a few glitches.

Holland
03-19-2010, 07:13 PM
Hi p45cal,
Works much better then my version THANK YOU very much,

Now what changes I have done not working though,

I removed the Upload and Clear button and want C3 to be the main source of control. When C3 is populated it loads great, now if C3 is blank or deleted can I get the below to happen, as well as get a pop up message if wrong Project code is entered currently if I enter a75 I get a run time error.

See below 2 codes, also when workbbok is open can I get a pop up with a message to use C3 to enter Project #,


Sub Clear()
If Sheets("Tool Audit List").Range("C3").Value = "" Then
Sheets("Tool Audit List").Range("B7:H10004").ClearContents
End If
----
With wsLog
If .Range("C3") = "" Then
MsgBox "Please Confirm Project Number"
.Range("C3").Select
GoTo Endo
Else
Findval = .Range("C3")
End If
End With

End Sub
OR
------

Private Sub cmdbtnHelp()

MsgBox "Please Confirm Project No,", _
vbInformation, "Help"

End Sub


thank you again
Holland

Holland
03-19-2010, 07:28 PM
Hi p45cal,
I figured out the Pop Up Message Macro As Worksheet Opens,

Option Explicit
Private Sub Workbook_Open()

Dim msg As Long

msg = MsgBox("Please enter project # into cell C3", vbOKOnly, "Test Test")

End Sub

mdmackillop
03-20-2010, 03:18 AM
Check first to avoid unneccessary message


If Range("C3") = "" then
msg = MsgBox("Please enter project # into cell C3", vbOKOnly, "Test Test")
End If

Holland
03-20-2010, 07:19 PM
Thank you mdmackillop, (http://www.vbaexpress.com/forum/member.php?u=87),
Can't get it exactly to work, maybe I am not placing it in the wrong part of the module. Suggestions?

Thanks,
Holland


Check first to avoid unneccessary message


If Range("C3") = "" then
msg = MsgBox("Please enter project # into cell C3", vbOKOnly, "Test Test")
End If

mdmackillop
03-21-2010, 03:36 AM
Sorry,
I missed the Sheet reference from my code. Try this instead

Private Sub Workbook_Open()
Dim msg As Long
Dim TestRange As Range

Set TestRange = Sheets(1).Range("C3") '<=== Adjust as required
If TestRange = "" Then
msg = MsgBox("Please enter project # into cell C3", vbOKOnly, "Test Test")
Application.Goto TestRange
End If
End Sub

mdmackillop
03-21-2010, 08:01 AM
Currently when the correct Text is entered in C3 the Upload macro runs perfectly.

What I would like to add if the wrong text is entered into C3, I would like a pop up window stating "Please reconfirm Project #" and I would then like "C3" to clear contents, and select C3.
I would also like to have the "Clear" macro run as well when "C3" delete key is used or the space bar, hopefully we can put 2 events in one cell.More or less I don't want any error messages because the end users all somewhat new to excel.

Better to use Data Validation to prevent your users from making illegal entries in the first place.

Holland
03-21-2010, 10:58 AM
Hi mdmackillop,
Cannot use Data Validation, that would be the easiest fix but my sample only shows 3 variables (A1, A2, A8 workbook) in reality there are many workbooks and new projects/workbooks get added on a daily bases.

I do require a Pop up Window similar to the validation but in a script, also when C3 is deleted or cleared I'd like the Clear macro to run. Please keep in mind that there is already a macro running once text is entered into C3.

Please help and thank you again,

Holland


Better to use Data Validation to prevent your users from making illegal entries in the first place.

Holland
03-21-2010, 11:04 AM
mdmackillop,
You are a genius, I removed the Data validation and it is somewhat working Thank you, now all I need is the "Clear" macro to run when "C3" is deleted or the space bar is used.

Fyi - When the space bar is used in "C3" the Clear macro does run, when I use delete though the Clear macro does not run.

I feel we are really close,...

Thank you,
Holland


Better to use Data Validation to prevent your users from making illegal entries in the first place.

mdmackillop
03-21-2010, 11:32 AM
I'd stick with the Validation if possible. I've added a blank in hidden row 2 which allows it to be entered and will trigger the Clear macro

Holland
03-21-2010, 11:48 AM
Ok I could stick with the Validation as I just test various other workbook projects and they did load fine, thank y. Somewhat still confused on the clear macro not working 100% for me. C3 + Clear macro works with the space bar great, still not working with delete though,. Any thoughts

Holland
and thank you again.

mdmackillop
03-21-2010, 12:10 PM
Merged cells!!!!!
These always cause problems. Unmerge C3 & D3 or adjust the Target Address in your worksheet change code.

Holland
03-21-2010, 12:27 PM
mdmackillop,
THANK YOU! Did exactly as you instructed and works like a charm! I sincerely appreciate your assistance and everyone's assistance on this site. By far the best site I have ever used.

Just going through vigorous testing now. And setting up the print to last cell used.

:beerchug:

Holland

Aussiebear
03-21-2010, 03:17 PM
Rather than using Merged Cells, try Centre Across Selection

Holland
03-21-2010, 04:36 PM
Thank you Aussiebear, tried it and works well.

Holland

Holland
03-21-2010, 04:40 PM
mdmackillop,
Ok after a lot of testing no errors were found,.. Only concern is when I convert it into a template nothing works. Only reason I want to convert to template because of various users... Any suggestions.

Thank you,
Holland


Merged cells!!!!!
These always cause problems. Unmerge C3 & D3 or adjust the Target Address in your worksheet change code.

mdmackillop
03-21-2010, 04:50 PM
Have you saved the new workbook before running the code. I didn't have any problems with it.

Holland
03-21-2010, 04:54 PM
Yes did a save as Excel Template,... :(


Have you saved the new workbook before running the code. I didn't have any problems with it.

Holland
03-21-2010, 05:06 PM
could it be that I am working in 2007 Excel and saving as a 1997-2003 template instead of 2007?

Holland
03-21-2010, 06:58 PM
This is the error message I get high-lited when saved as a Template and I try to run any macro from C3

ChDir ThisWorkbook.Path

Any suggestions?

Thank you,
Holland


Have you saved the new workbook before running the code. I didn't have any problems with it.This

Holland
03-21-2010, 07:05 PM
Ok I tried right clicking the workbook (template) to open it and it worked (all the macros), but if I double click the template and a workbook opens in a sequence Ex(workbook is the template, workbook1, workbook2 etc, will not work)...

Thank you,
Holland