PDA

View Full Version : Concatenate (Nest) "IF" Commands ??



Rlb53
10-11-2012, 03:44 AM
I desire to keep a command button hidden until an absolute number of comboboxes and textboxes have been filled with data.

After hours of failing with this... I opted to allow the command button to be visible, but I would like to keep it from being operable until all data has been entered, but provide an error message if it is selected prior.

I have been attempting to nest the "IF" Command, but it is not working with the syntax I have prepared. I am not getting an error message, but it is giving me a false statement with my testing.

The Nesting consists of 14 conditions.


If Not Sheets("sheet2").BOLsheetcustomerlist = "" & _
Not Sheets("sheet2").BOLSheetCity = "" & _
not sheets("Sheet2").BOLSheetProduct="" Then
MsgBox "All Fields Have Been Entered.
Else
MsgBox "All Fields Have Not Been Entered.
End If


I've also tried to nest the command as:


If Sheets("sheet2").BOLsheetcustomerlist = "" or _
Sheets("sheet2").BOLSheetCity = "" Then
MsgBox "All Fields Have NotBeen Entered.
Else
MsgBox "All Fields Not Been Entered.
End If


Could you assist ? The Goal is to have all data entered prior to Save.

Thank you !

Simon Lloyd
10-11-2012, 04:22 AM
It would be good to see a sample of your sheet!, does there need to be a value in every combobox & textbox?

snb
10-11-2012, 05:17 AM
sub snb()
with sheets("sheet2")
for j=1 to .oleobjects.count
if instr("ComboBoxTextBox",typename(.oleobjects(j).object))>0 then
if .oleobjects(j).object.value="" then exit for
end if
next
.commandbutton1.visible= j=.oleobjects.count+1
end with
end sub

Rlb53
10-11-2012, 08:06 AM
Thanks Guys....

I'm going to have to play with the code you suggested SNB... I changed the name of the command button... but obviously have to make additional changes... (Just finished rebooting from a complete lockup..)

I'll throw a generic sheet together for review... but.. to answer your question Simon... unfortunately... the data that is being gathered is considered "FINAL with NO MODIFICATIONS" after it is stored, so the purpose of preventing an unintentional "Save" is pretty important, therefore the desire that all information is complete and accurate is strong.

(I really need to be doing this in Access.... but I'm trying to get something out reasonably quick for admin to review the concept and I have no clue with Access yet.)

Thanks !!

snb
10-11-2012, 08:24 AM
I edited the previous suggestion to make it easier to maintain/adapt.

Rlb53
10-11-2012, 09:45 AM
Attached is a sample of what I'm "Attempting" to accomplish.

Thanks !