UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Excel VBA If Statement Help., Any Version    
 
   
bazza
post Nov 13 2017, 11:01 AM
Post#1



Posts: 319
Joined: 13-February 03



At the start of my code I have

CODE
Application.ScreenUpdating = False
Sheets("Sheet1").Visible = True
Sheets("Sheet2").Visible = True
Sheets("Sheet3").Visible = True


And at the end of my code I have

CODE
Application.ScreenUpdating = true
Sheets("Sheet1").Visible = False
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False


As these are three hidden sheets that need referencing when I run my code.

Now in the middle of my code I have:-

CODE
If Dir(strpathmain & strfilemain) <> "" Or Dir(strpath & strfile) <> "" Then
        Ans = MsgBox("A Transmittal with this number already exists. Overwrite?", vbExclamation + vbYesNo, "Overwrite existing Transmittal?")
        If Ans = vbNo Then Exit Sub
    End If

'check drawings are selected
Sheets("Sheet1").Select
If Application.WorksheetFunction.CountA(Selection) > 0 Then

If MsgBox("Are you sure you want to produce a Transmittal?", vbYesNo) = vbNo Then
Exit Sub
End If
Else
MsgBox "No Drawings Selected", vbCritical
Exit Sub


The problem I'm having is that when my MsgBox answers are no and I exit sub the sheets are still unhidden and the screen updating is still set to false. I have tried adding extra lines into my IF statements and using ElseIF to return the sheets visibility to false but Im making a mess of it. Can anyone point me in the right direction.

I basically need the vbNo to change the visiblity of the sheets back and then exit sub.

Racking my brain!
Go to the top of the page
 
kfield7
post Nov 13 2017, 11:19 AM
Post#2



Posts: 774
Joined: 12-November 03
From: Iowa Lot


If Ans = vbNo Then Exit Sub

I dont' see where this line tells the spreadsheet to do anything before exiting.

You need

If Ans = vbNo Then
{do something}
Exit Sub
end if

same with

If MsgBox("Are you sure you want to produce a Transmittal?", vbYesNo) = vbNo Then
Exit Sub
End If
Go to the top of the page
 
bazza
post Nov 13 2017, 12:24 PM
Post#3



Posts: 319
Joined: 13-February 03



Hi

Sorry I forgot to say that I am putting code where you have put {do something} but it seems to be ignoring it and going straight to exit sub.
Go to the top of the page
 
JonSmith
post Nov 13 2017, 01:56 PM
Post#4



Posts: 3,158
Joined: 19-October 10



I disagree with kfield7 that you should write some code before your exit sub. You'll end up duplicating the code at the bottom which is bad. I suggest you use a label and a goto.

To do this write 'Cleanup:' above the code the second code snippet you provide.
Now replace your Exit Sub with 'GoTo Cleanup'.

Now instead of directly exiting it'll skip to the bottom and you dont need to duplicate any screenupdating or visibilty.
That being said, if screenupdating is off isnt hiding the sheets redundant?
Go to the top of the page
 
bazza
post Nov 14 2017, 05:54 AM
Post#5



Posts: 319
Joined: 13-February 03



Getting lost amongst my own code here!

I put the code in but was getting block IF without end IF error. I have a peculiar problem now.

I have the following code on a command button which ensures the range selected is columns A-E. If it isn't then a message box appears "Please select correct columns" This all works fine

CODE
If Selection.Address <> "$A$" & Selection.Row & ":" & "$E$" & Selection.Row + Selection.Rows.Count - 1 Then
MsgBox "Please select the correct columns", vbCritical
Exit Sub
End If


Now I want some sheets to be hidden if the correct rows arent selected and the message box appears. So I've done this:

CODE
If Selection.Address <> "$A$" & Selection.Row & ":" & "$E$" & Selection.Row + Selection.Rows.Count - 1 Then
MsgBox "Please select the correct columns", vbCritical
Sheets("Sheet1").Visible = False
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Exit Sub
End If


As soon as I add those three lines the first line no longer works and its not recognising that
CODE
selection.address
is the same as
CODE
"$A$" & Selection.Row & ":" & "$E$" & Selection.Row + Selection.Rows.Count - 1
even though Im selecting the same cells. Im perplexed
Go to the top of the page
 
JonSmith
post Nov 14 2017, 06:47 AM
Post#6



Posts: 3,158
Joined: 19-October 10



Like I said, don't complicate your IF's with extra stuff, replace Exit Sub with a GoTo
Go to the top of the page
 
bazza
post Nov 14 2017, 07:45 AM
Post#7



Posts: 319
Joined: 13-February 03



I have done but as soon as I enter anything other than just exit sub, the first line of the code stops working!
Go to the top of the page
 
bazza
post Nov 14 2017, 08:02 AM
Post#8



Posts: 319
Joined: 13-February 03



For some reason the code runs as normal if all sheets are visible but if some are hidden it doesn't. This is strange as my command button is on a sheet that is always visible.
Go to the top of the page
 
JonSmith
post Nov 14 2017, 08:08 AM
Post#9



Posts: 3,158
Joined: 19-October 10



QUOTE
That being said, if screenupdating is off isnt hiding the sheets redundant?


From my post above
Go to the top of the page
 
bazza
post Nov 14 2017, 08:27 AM
Post#10



Posts: 319
Joined: 13-February 03



I have my sheets set to XlVeryHidden so I need to unhide them before I run any code that involves them.
Go to the top of the page
 
bazza
post Nov 14 2017, 08:36 AM
Post#11



Posts: 319
Joined: 13-February 03



Think I've sorted it now.

Thank you very much for your help. Much appreciated.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    13th December 2017 - 07:29 PM