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 attempting to format some TextBoxes from within a For/Next loop. I need a way to check which TextBox is the active TextBox in the loop. Using i as the variable, I came up with this code snippet: Me.Controls("TB" & i).Text = Format("TB" & i, "mm/dd/yy")
If i = 3, this gives me in TextBox3 (which is called TB3) the text 'TB3' and not the value of what is in TB3. It has got to bo something simple, I just can't see it!!!
I need to display Excel Sheet in a Browser and I want to disable the Right Click and Menu of Excel(File, Edit).
So in the method Workbook_Open i coded following
Private Sub Workbook_Open() Application. CommandBars("Worksheet Menu Bar").Enabled = False Application.CommandBars(" Cell").Enabled = False Application.CommandBars("Sheet").Enabled = False Application.CommandBars("Ply").Enabled = False Application.CommandBars("Row").Enabled = False Application.CommandBars("Column").Enabled = False End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.CommandBars("Worksheet Menu Bar").Enabled = True Application.CommandBars("Cell").Enabled = True Application.CommandBars("Sheet").Enabled = True Application.CommandBars("Ply").Enabled = True Application.CommandBars("Row").Enabled = True Application.CommandBars("Column").Enabled = True End Sub
It works fine, but if I open Excel application before viewing this HTML page(which contains XLS) and then the option are also disabled in Stand alone Excel application. I want to disable option when for this Sheet which is opened.
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
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 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
After a user has selected the required option buttons, is there a way to disable them without deleting, so that they are still visible (for history tracking) but not modifiable?
'09-- Check metrology info If Worksheets(" Split Lot Info").MetrologyYes.Value = True Then Worksheets("Split Lot Info").Shapes("MetrologyYes").Enabled = False Worksheets("Split Lot Info").Shapes("MetrologyNo").Enabled = False If Worksheets("Split Lot Info").MetrologyNo.Value = True Then nResponse = MsgBox("Are metrology steps set up?", vbOKOnly, "Missing information") Worksheets("Split Lot Info").Shapes("MetrologyYes").Enabled = False Worksheets("Split Lot Info").Shapes("MetrologyNo").Enabled = False End End If End If
I am trying to allow uses of a spreadsheet to be able to select one control button which would in turn disable another one via check boxes however the control buttons do not re-enable when the undo check box is selected.
I have a sheet with 30 textboxes made with the drawing toolbar. The user enters data, then clicks on a button which formats the page to adjust row size to match amount of info. I am having trouble finding the code to store the currently selected textbox in a variable so that I can place the focus back to the same textbox when the macro ends. Can anyone help me with the code to get the active textbox?