I'm trying to pull up a second form from a command button within a form. There's a command button in a sheet to open the first form (frmOrderInput.) Then there's another command button in that form to open the second form (frmPriceInput.)
i know in VB6 i could disable the X in the corner so someone can't X out of my form, and i see that's not an option in VBA.
So how can i stop the form from terminating when the user clicks that? i want them to be forced to use one of my buttons to close out and finish.
i tried adding something simple like Exit Sub to the Userform_Terminate() sub, but nothing. i can make code run when they click that, but i can't stop it from closing. what to do?
also, the form is being called by macro that does alot of other stuff before and after the form, so just opening a new instance of the form won't help unless you can stop my other code from continueing until the form is actually done...
This is probably really straight forward but cant see why it happens, the following macro works fine when called by a button created by the form toolbar but doesnt when called by a command button, get the runtime error 1004, "select method of range class failed"
I have a user form with four pages. I would like to disable pages 2 and 3 if the checkbox on page one is cheked. I know the pages have a property called "Enabled" but i can't figure out how to code this.
The user form is called frmAnvilProgNew The pages are called Page1, Page2, Page3, and Page4 The checkbox is called Check1
see the attached workbook. Is this possible? Should I be using tabs instead of pages?
I want to have a Checkbox (Form) that is in my Worksheet to disable/gray-out once it is selected. I don't want people to be able to uncheck it again. I want them to be forced to click another Checkbox to enable it again.
Check Box 1 = "Apply" Check Box 2 = "Delete"
Once "Apply" is checked, gray it out. This will force a user to click "Delete" in order for the "Apply" button to be enabled again. I do not want a user to click "Apply" once selected as their way of 'deleting' the information.
I am imagining a spreadsheet with the equivalent of an info button in each cell of column A. The idea is the user clicks the button to open a User Form containing additional info about the entitity represented by values on that row.
E.g., Row 1 is labels, row 2 is all about Bob, 3 is about Jane, 4 is about Freddy. If the user clicks the info button on row 3 then a user form opens up and displays additonal info about Jane.
I am wondering if there is a way for VB to identify which row the clicked button was on without having to write individual code for each button telling VB which row the button was on.
I am hoping there is some form button property I can reference or interrogate which will give me a cell address; a button.position property or similar. This will obviously save me a lot of coding if VB can dynamically determine which row is being interrogated as there will be a couple of hundred of these to start with, potentially thousands. (I realise Access is a better solution for database interrogation, but I have to work with excel at present).
Ultimately, the user form that the button calls up will have a list box on it and I want the list box to default to the entity on that row, and allow the user to browse the other entities if they want to.
If there isn't a solution for this I will just have a single info button for the users to click that opens the user form and then they can select the entity from a list box. But I would like to offer the "intelligent display" option but don't relish coding the equivalent of several hundred "You clicked button four hundred and seventy six, default to info card for bert".
I want to disable the X button on the top right hand corner in excel, but still allow the user with the option of closing the workbook via File and then scrolling down to EXIT. (Not close). I have disabled most of the functions in the Menu bar Close being one of them.
So only Exit is now enabled. When I click on the X it give me the following message as shown below advising me to go to the File menu to Exit, this bit I am happy with. However this is were my problem begins, when I go to File and Exit I get the same message as below and can not exit the application.
Private Sub Workbook_BeforeClose(Cancel As Boolean) Cancel = True MsgBox "Please select EXIT from the File Menu.", vbCritical, "Cannot Close" End Sub
I am creating a form that performs several different functions. I would like to force a sequence of these functions by enabling/disabling the controls based on a variable.
For example, a form as two buttons Button1 and Button2. By default, Button1 is ENABLED and Button2 is DISABLED. When Button1 is clicked, a macro is run, where an Enable_Button2variable is initialized to FALSE. The last line of the macro sets the Enable_Button2 variable to TRUE. Then, because Enable_Button2 is now TRUE, Button2 then becomes ENABLED.
I am trying to apply this strategy to a ComboBox, ListBox and CommandButtons. I'm not sure what event to trigger on to poll the Enable_Button2 to enable/disable the control.
The code below is supposed to disable the appropriate command buttons when it's associated cell values are 0.
Private Sub Worksheet_Activate() If Range("C17").Value = 0 Then ActiveSheet.CommandButton7.Enabled = False ActiveSheet.CommandButton4.Enabled = False Else ActiveSheet.CommandButton7.Enabled = True ActiveSheet.CommandButton4.Enabled = True End If
Not working ..... no buttons are disabled even with their cells are zero. Did I forget something?
I would like some help on disabling the Esc key in a macro and to disable the Cancel button and X on message and input boxes.
My macro is to insert rows in a protected worksheet and another to run a spellcheck, if my users hit any of the above keys during the macro my worksheet is left unprotected and formulas could be deleted in error. I have pasted a copy of my code below,
Sub InsertRowAboveCopyFormulas() ActiveSheet.Unprotect ("password") Dim Rng, n As Long, k As Long Application.ScreenUpdating = False Rng = InputBox("Enter number of rows required.") If Rng = "" Then Exit Sub On Error Resume Next ActiveCell.EntireRow.Select Selection.Resize(rowsize:=Rng).Rows(1).EntireRow. _ Resize(rowsize:=Rng).Insert Shift:=xlUp Selection.Offset(Rng).AutoFill Selection.Resize( _ rowsize:=Rng + 1), xlFill Selection.Resize(Rng).EntireRow. _ SpecialCells(xlConstants).ClearContents ActiveSheet.Protect ("password") End Sub
I'm sure there is a way to disable the 'X' (quit) button in the top right-hand corner of the EXcel application (not the 'X' for individual workbooks), or the hook the quit event so that I can run code that shut down the application cleanly? I'm sure a Windows API call is involved but do not know enough about that side of programming to know for sure.
I have buttons on each of my sheets, when one is clicked code is called in my module ("modAPI"), and from that code I wish to disable and enable certain buttons, and/or change the caption of those buttons. However when I try to do:
With ws btnRefresh.Enabled = False End With
I get Error 424, Object Required. I've tried recording a macro of what I wish to do, and it gives me ActiveSheet.Shapes("btnRefresh").Select, but this doesn't give me the option to enabled/disable or change the caption of the button.
I have two option buttons on a user form, one for a temperature of <250 & one for a temperature >250. I want the option button >250 to be greyed out or have it so that you cannot choose it & option button <250 selected if a certain criteria in a list box is selected. The list box is called Valve_Model & the criteria i want it to work on is if the valve model HPBV Soft is selected & HPBV Soft AC.
I'm using the following code to create an Inputbox but I want to either disable the OK button until an entry is made or not allow a blank entry. Forcing the user to have to make an entry.
If NameTextBoxnew.Value = "" Then NameTextBoxnew.Value = Application.InputBox("You must input a name/nickname to identify person", "Name") End If If SexTextBoxnew.Value = "" Then SexTextBoxnew.Value = Application.InputBox("You must select a sex(m/f)", "Sex") End If If DOBTextBoxnew.Value = "" And AgeTextBoxnew.Value = "" Then AgeTextBoxnew.Value = Application.InputBox("Please insert a rough age in years", " Date of RSIT") End If
I have a userform with a textbox and a command button. I have used data validations on the textbox. Clicking the command button saves the data in the worksheet. It works well if the user hits tab, enter or down arrow key after entering the data. The problem is, the textbox validations fail to get executed if the focus stays on the textbox and command button is pressed. I want the command button to be disabled until the user has moved the focus away from the textbox -there by executing the validations!
I am making a timesheet which has a UserForm with textbox and 3 command buttons namely Search, IN and OUT. The program works like this, user type his id no., then clicks on search, once id match command buttons for IN and OUT are enabled. User clicks on IN button then current time which tags as NOW() will be pasted on the worksheet. Same goes with the OUT button. I was able to make them work that way. The problem here now is, the data being entered are vulnerable for data-re entry. What I mean is, if a certain user has already a recorded IN time, if he clicks on the IN time again, the data will be overwritten. Is there a way that once the user has already a recorded IN time, the IN button will be disabled for that user since he has already a recorded IN data?
I have made an excel workbook detailing team performance that all of my team have access to and that requires updating once a day. The first person to access the workbook each day presses a Command Button that updates the workbook and inserts a time/date stamp. However, other members of the team go into the workbook and update it again the same day which then affects the data being reported.
I know i should probably just sack them for not being able to read the time and date but HR would not like that so is there a way to disable CommandButton1 (ActiveX) so that once it is pressed it is disables until the next day?
I have a button in a template file, that when clicked, saves the file as a new file with file name based on data in certain cells.
I want to disable the ability to save the file with the Save icon or Ctrl-S, but the button still needs to be able to save it (the button is technically doing a Save As, so no problem there). But, I, as the administrator of the template, need to somehow be able to save updates to the template file. Can this be accomplished by coding Save to work when used with a secret key combination that only I know (I can put it in password-protected VB code)?
Then, as if that's not tricky enough, the trickier part is that once the file is saved as the new file (no longer the template) - re-enable the Save button and Ctrl-S so the user can then save it as many times as they want.
I am trying to disable a command button if 4 textboxes are empty. I can make it work if one of the textboxes are empty but not all of them. this is what i have so far.
Private Sub TXT3_Change() If TXT3.Value = "" Or TXT4.Value = "" Or TXT5 = "" Or TXT6 = "" Then CommandButton13.Enabled = False End If If TXT3.Value "" Then CommandButton13.Enabled = True End If End Sub