I have a macro with a vbYesNo in it currently, I tried adding cancel as an option (vbYesNoCancel) but ran into some issues. aMaking my code run with the Cancel option? All I need is to have the cancel option there and have the sub end if cancel is selected.
Code:
Sub Sendit()
Dim lr As Long
' Conformation Box for Daily Sales Report Date[code]......
I have an inputbox in my code. By default the inputbox is giving the options "OK" and "Cancel". Is there a way where I can have only "OK" option in my inputbox.
View 3 Replies View RelatedThe below code works well. However I would like to give the users an option to opt out of running the code when the message box appears.
Code:
Sub delete_sheets()
Beep
MsgBox "This will delete All Sheets but the active sheet"
*****this is where I would like to use an additional message box or include it in the above message box an option for the user to cancel this code****
Range("XA1") = ActiveSheet.Name
ActiveSheet.Name = ("Delete_Sheets")
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In Worksheets
If Not ws.Name = "Delete_Sheets" Then ws.Delete
Next ws
[code]....
I have a survey with different groups of Control Toolbox options buttons on it. I want to ensure that each question has an option button selected before the survey can be exited and emailed onwards. The grouped button names are: GroupA, Group1 through to Group6.
View 2 Replies View RelatedIn my userform I got different comboboxes and one checkbox.
I added a vbOkCancel to my checkbox but for some reason when I click on ok it does not close the userform and therefor not write it into my worksheet.
Can you please tell me what I have to change so it would work again.
The code is below.
Private Sub cmdOk_Click()
Dim RowCount As Long
Dim ctl As Control
Dim Stunden As Double
I highlighted the chxbox event so you can see it better.
I have a code that prepares my spreadsheet and then prints out around 6 different sheets when all is said and done. The prints are not together in the code. I was wondering if there is something that I can add at the beginning of the code to ask the user IF they want a Printout. If they answer Yes then they get printouts of the 6 or so sheets and if they answer No then the code will run but no printouts occur???
I have play3ed around a bit and implemented the following in one of my codes but this one only has 1 sheet that prints out.
'Checks to see if this is a corrected Deposit in which case Depositor probably has a Verification sheet already printed
If Sheets("Input Sheet").CheckBox5 = True Then
Answer = MsgBox("It's seems that this Verification sheet was already printed once. Do the Corrections you have made require you to print another one?" & vbCr & vbCr & "If you reuire the Verification sheet to print again the please click YES otherwise click No.", vbYesNo, "PRINT Out Required???")
If Answer = vbYes Then
Rows("16:275").Select
ActiveSheet.Unprotect "unlock"
Selection.AutoFilter
ActiveWindow.ScrollColumn = 1.........
following code be write down in more efficient manner :>>>
Range("B3") = Range("L" & Target.Row)
Range("C3") = Range("O" & Target.Row)
Range("D3") = Range("Q" & Target.Row)
Range("E3") = Range("R" & Target.Row)
I have written a sub that will compare the data in one colum to the data that i paste in another colum, when it finds a similarity it deletes the row. Everything works fine, but the file that needs to run the code will be changing every month. how do i export or package to use in other sheets. Also what command will i need to insert to get the new sheet to run the code.
View 7 Replies View RelatedWondering where this code can be adjusted to be much more efficient. Right now it is going through 1000 rows and 14 columns and it is taking about 10 minutes. I eventually need it to go through 6000 rows and 32 columns..
I already stored the HexToDec(Cells(x, 1)) result to variable H2D and it didn't really make too much of a noticeable difference.
I have the following code to do something similar to above but I need to add the filepath as a hyperlink to the email along with the body text. I have 4 cells in the Excel workbook that contain 1) Who to sen to:, 2)The Subject:, 3) A sentence for the Body of the email and 4) The filepath of the workbook as a hyperlink.
I want to add both the Body text and the hyperlink to the email so that the person opening the email can click on the link and open the file.
[Code] ....
I would like the following code to be run for all those sheet/s that has a name = "single" (Not case sensitive neither an exact match) of my active workbook.
Code:
Sub UIUIUI()
Dim LR As Long, i As Long
LR = Range("I" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With Range("I" & i)
If .Offset(, -1).Value = 1 Then .Value = .Value & "-"
End With
Next i
End Sub
I have a login page for my spreadsheet however a user can simply view the code to get the login username and password.
View 2 Replies View RelatedThe below code uses data in column "A" (Const cl& = 1) to create a sheet for every unique value in column "A".
I would like for the code to request what column to use as source data. As an example when the code first runs a pop-up box would ask for a column letter to use as the source, after entering the letter (or corresponding column number) the code will execute.
Code:
Sub Add_sheets_from_A()
Range("XA1") = ActiveSheet.Name
ActiveSheet.Name = ("Add_Sheets")
[Code]....
I want to write a VBA code that will place ActiveX Frame directly onto the worksheet and then add 3 Option Buttons from Toolbox into that frame.
I could not find an answer searching through the web... Also when trying to record macro and see the code it did not record placing the buttons from the toolbox or any formatting (color, caption, etc.) via properties...
When formatting the buttons it would be useful if the selected radio button would have different color, bold font, etc.
I was working on a presentation for work where I wanted to build a 'Family Feud' type board to play a game. In the board I am using activex text boxes with code that would hide the text box to reveal the answer underneath or it would show a custom shape (an X in a box) and play the buzzer sound. The code is pretty simple, first I would make the shape visible, then I would play the sound then the shape would be made invisible. If I step through the macro everything works fine, but when I run the macro, you never see the shape. It's almost as if the sound plays before the shape shows up and then it is made invisible again. I tried putting a wait and a sleep command between making the shape visible and playing the sound but that made no difference. If I remove the code to hide the shape at the end and run the macro, the sound plays and then the shape appears. Is there anyway to have the shape appear prior to or at the same time as the sound plays?
Code:
Private Sub CommandButton8_Click()
Application.ScreenUpdating = True
ActiveSheet.Shapes("First Strike").Visible = True
Play_Strike_Sound
'The previous line refers to another macro that has the code commented below. The sndPlaySound32
'function is one I picked up from cpearson.com
'sndPlaySound32 "C:\_Fin SysSoundsff-strike.wav", SND_SYNC
ActiveSheet.Shapes("First Strike").Visible = False
End Sub
I have set up a combolist box, and wish to run different VBA subroutines depending on what the user has selected from the combolist. How do I do this? Apologies in advance if this sounds like a silly question.
View 2 Replies View RelatedI have a sales spreadsheet, that people on this forum have very kindly helped me with by giving me two macros; one to remind users that they need to update the month cell when an order comes in, and the other to automatically put the date in a cell when any cell in that row is changed.
The final thread is here: Message To Remind That Cell Is Mandatory
and the Macro used is:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Application.EnableEvents = False
For Each c In Target
If c.Column = 11 Then
If c.Value = "100 - Purchase Order In" Then
MsgBox "Is the Month In correct?"
End If
End If
If c.Column > 1 And c.Column < 18 Then
Cells(c.Row, 1) = Now
End If
Next c
Application.EnableEvents = True
End Sub
However...
We've now found that we cannot undo anything in these spreadsheets. If, for example, a cell is incorrectly copied or deleted, the only way of undoing the change is to shut down the spreadsheet without saving!
Is this just a by-product of using the time macro (a search on other threads suggests that it might be), and, if so, is there any way of changing it?
is it possible to choose an option button which will then perform a calculation on a cell reference? I’m trying to deduct a percentage from a total when an option button is highlighted.
View 5 Replies View RelatedI have several option groups (Y & N in each) linked to cells to display TRUE / FALSE depending on which option is selected in each group. I have another cell which I want to apply conditional formatting to if EITHER Y or N is selected in all groups. At present I use COUNTIF to check for FALSE=0 in the linked cells which works if all the answers are Y. How do I apply CF if there is a mixture of Y & N (TRUE / FALSE) in all groups (I'm not sure if I've explained that well or not).
Excel 2013
How can I disable the cancel button when the user is prompted to do a "save as"? I need to force the user to name the file and save it to the appropriate file - if they are allowed to cancel I run into problems with my macro.
View 12 Replies View RelatedI've created a routine that starts with a message box saying the following:
"Please Confirm 'DTmacTest.xls' is the ONLY open Excel workbook"
So I have an "OK" button and a "Cancel" button.
but no matter which one I press, it runs the routine.
How do I assign the cancel button to stop the routine when it's pressed?
In the below mentioned code, there is some problems which need to be modified.
Sub printall()
Dim wsAtt As Worksheet
Dim wsPay As Worksheet
Dim rng As Range
Dim cl As Range
Application.Dialogs(xlDialogPrinterSetup).Show
On Error Goto PrintAll_Error
Application. ScreenUpdating = False
Set wsAtt = ThisWorkbook.Worksheets("Register")
wsAtt.Select
Set rng = wsAtt.Range(Cells(8, 1), Cells(Rows.Count, 1).End(xlUp))
Set wsPay = ThisWorkbook.Worksheets("Payslips")
when the code is run, a window for selecting printer pops up and if the user clicks OK button, it starts printing but the problem is that if the user clicks "Cancel", also it starts printing.
I want that if the user cliks "Cancel" it shuold not start printing & the Select Printer screen should vanish and return to the sheet from where tehe user has run the code.
The code was provided to me in response to my post earlier at this forum However the code line related to select printer is put by me.
By clicking the print-button the user may select the area to be printed.
But if I click the Cancel button, or the upper right X, the print message is sent to the printer, even it shouldn't.
How do I write the code for cancelling the print when clicking the cancel button?
Please see the VBA editor in the attached file.
A last question: is it necessary to declare the variable?
How can this be edited so when the savebox comes up when i hit save it saves the file but when i hit cancel it goes to error:
View 14 Replies View RelatedI have an input box which has OK and cancel buttons If the user clicks cancel I would like to routine to end, which I can do with exit sub. However this particular sub routine has been called from another so the rest of the code continues. Is there a way of exiting all the routines on cancel -- or a neater way than exit sub ?
View 4 Replies View RelatedIf I press either the "OK" or "Cancel" button without entering any states in (because I changed my mind and want ALL states in there, or I pressed either button by accident), the macro eliminates ALL the rows because it needs criteria.
Can this code be modified so that the message box does the following...
1. If I press the "OK" button but I haven't entered in any criteria in the message box, bring up another message box saying "you have not entered in any states in" which brings up an "OK" box and brings you back to the original message box.
2. If I hit the "Cancel" button, bring up a message box that says "No states will be removed" with an "OK" button, essentially cancelling the "State" macro portion of the sub, but continuing with the rest of the code in the sub if there is any.
I would like to have this message box work this way even if states were entered in the input box but the user pressed the "Cancel" button anyway.
3. Create a NEW button named "Keep ALL States" which essentially does the same thing as the "Cancel" button. (I'm trying to "idiot proof" this).
i have a code that i am using but however when I click on the cancel it is giving me a false in the cell when all I am want is to cancel and leave the information in the cell. Here is an example of the code i am using.
View 4 Replies View RelatedMsg = "Enter a number"
BOXTITLE = "Number"
ANS = InputBox(Msg, BOXTITLE)
This generates 2 buttons. If i click the cancel button how would i direct the code to go to a certain line in my code. EG if cancel button clicked then goto 10
After an InputBox statement, how do I test to see if user pressed "Cancel" and if so, exit the Sub?
View 9 Replies View RelatedHere is my
Private Sub Workbook_Open()
Select Case MsgBox("my question?", vbYesNoCancel)
Case Is = vbYes
'Yes Code will sort Moistures and Owners
Case Is = vbNo
'NO code will sort Moistures and Fields
Case Is = vbCancel
' Will Sort neither and allow for data entry
Case Else
Debug.Print "Whoops"
End Select
End Sub
I want to rename the Yes, no, and cancel buttons to Pay Owner, Field Close Out, and Data entry.