Solved: Form not Uploading

[Holland]

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]

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]

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 #,


[VBA]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[/VBA]


thank you again
Holland

[Holland]

Hi p45cal,
I figured out the Pop Up Message Macro As Worksheet Opens,

[VBA]Option Explicit
Private Sub Workbook_Open()

Dim msg As Long

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

End Sub[/VBA]

[mdmackillop]

Check first to avoid unneccessary message

[VBA]

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

[/VBA]

[Holland]

Thank you mdmackillop,,
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

[vba]

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

[/vba]

[mdmackillop]

Sorry,
I missed the Sheet reference from my code. Try this instead
[vba]
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

[/vba]

[mdmackillop]

Currently when the correct Text is entered in C3 the Upload macro runs perfectly.

1. 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.
2. 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]

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]

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]

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]

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]

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

[Holland]

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.



Holland

[Aussiebear]

Rather than using Merged Cells, try Centre Across Selection

[Holland]

Thank you Aussiebear, tried it and works well.

Holland

[Holland]

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]

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

[Holland]

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]

could it be that I am working in 2007 Excel and saving as a 1997-2003 template instead of 2007?