I have some code which formats a worksheet based on user selections in a form. The code works, except when the user has made the same selections in two multi-select list boxes. I need to find a way to determine whether the code has already formatted the worksheet.
To me, it seems that there might be two ways to do this, 1) have a test on the original block of code that checks whether this portion of the code has already executed for this variable or 2) check the worksheet for the string (the code formats the same string each time but adds the variable for the current iteration of the loop only if Year1 = True.) The problem is that I only want to format the column if it hasn't already been formatted for that selection.
If CostShare = True Then
For Each ctrl In UserForm2.Controls
If TypeName(ctrl) = " ComboBox" And ctrl.Visible = True Then
If ctrl.Value = "Cost-Share" Then
t = ctrl.Tag
For Each ctrl2 In UserForm2.Controls
If TypeName(ctrl2) = "ListBox" Then
If ctrl2.Tag = t Then
With ctrl2
For ii = 0 To .ListCount - 1...............................
i want to know how to show the progress of code exceuted in an excel sheet. like when we download a file from web or server it shows the staus of the file transfer.
When I close my worksheet excel asks me if I wish to save the file one last time, if I click 'yes' I would like some more code to be executed and then have the spreadsheet save itself and close definitively, how can this be done using vba?
i have this code which askes the user for a job number once the workbook is opened.
Private Sub Workbook_open()
' If sheet was named by original open routine, exit
If ActiveSheet.Name = "Main Roof" Then Exit Sub
' otherwise
Do Returnvalue = InputBox("Please Enter a New Job Number.", "Information")
' Allow changes by entering q as the Job Number If Returnvalue = "q" Then Exit Sub
' Delete the ' from the front of the following two lines and ' then when you enter q as the Job Number you will also be ' asked for a password. The default password is toe.
what iam trying to do is get it to open the userform "WorkSelection" after it has completed the above code.
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 inherited support for the an Excel 'program' that seems to be fairly unstable. I am currently having problems with the following code in a subroutine (executed when the user presses a command button):
With ActiveSheet.PageSetup .PrintTitleRows = "$1:$7" .PrintTitleColumns = "" .PrintArea = ActiveSheet.UsedRange End With
Sometimes it works and sometimes I get the following error: Run-time error '1004' Unable to set the PrintArea property of the PageSetup class.
… on the .PrintArea = ActiveSheet.UsedRange
I am having a hard time figuring out what is causing its wishy-washiness. Running the following code (launched from worksheet named 'consumer' with a command button):
The below code tests a particular cell before displaying the userform. If Range ("Bal_BF") contains an error (such as "#N/a"), a message box is displayed and the form should NOT display.
However, the code is still trying to display the userform, and gives me a error message "Object variable or With Block variable not set".
When Bal_BF contains a value, the form displays correctly.
Private Sub UserForm_Initialize()
If Employee = "" Then Call Retrieve
If IsError(Range("Bal_BF")) Then Call MsgBox("Your name doesn't appear in the Holiday & Absence file." & vbCr & vbCr & _ "Please see Leila Fretton or Neil Jimack about this.",.........
Let's say I have a long macro and I want to test some code a ways down in the macro, but I don't want to have to run through all the code to get there, because I already have my Excel spreadsheet set up at the point I need to check and I just want to start checking the code a ways into the procedure.
Short of commenting out all the code above where I want to start, is there another easier way to do this?
I am trying to determine Long Term Gain (LTG,) Long Term Loss (LTL,) Short Term Gain (STG,) Short term Loss (STL,) or No Loss nor Gain (NGL)testing two cells (A1 and B1)and setting a third cell (C1) to the text LTG, LTL, STG, STL, or NGL depending on the results of testing cells A1 and B1.
A1 represent a number of years and B1 represent gains or losses (negative)in dolars.
The way I see the logic is as follows:
If cell A1 or cell B1 are either one of them equal to 0, then it is neither a Gain nor a Loss (NGL.)
If cell A1 is greater than or equal to 1, then it is Long Term; else, if A1 is greater than 0 and less than 1, then it is Short Term.
On the other hand, if cell B1 is greater than 0, then it is a Gain; if B1 is less than 0 (a negative number,) then, it is a Loss.
I need to find (if it is posible in Excel) one formula to test the two cells for posible outcomes:
If A1 = 0 then C1 = NGL If B1 = 0 then C1 = NGL If A1 >= 1 and B1 > 0 the C1 = LTG. If A1 >= 1 and B1 < 0 the C1 = LTL. If A1 < 1 and B1 > 0 then C1 = STG If A1 < 1 and B1 < 0 then C1 = STL
I'm trying to identify the Active Cell for the formula that is executing.
Currently, using VBA, if the last manually clicked active cell (where the cursor resides) is different than the cell containing the executing formula I get the wrong cell, row, or column using ActiveCell.
My question is: I have a macro created for my Excel. I have a short cut key to run this macro. When i run the macro, the VB editor is opened where i have written the code. My requirement is, I don't want the VB editor to be opened when i run the macro. I want the macro to be executed but the VB editor should not be visible to the user.
I have a macro which output depence on the variable input. Input is represented by drop-down list. Before, I was choosing input from drop-down, then pressing the button my macro was running. Now, I'm thinking to if it's possible to exclude a spare operation from button pressing, just by clicking from drop-down.
My drop-down list is created through "Data->Validation->List box"
I got two worksheets visible for user to enter information with vba codes behind the sheets(i.e. Private Sub Worksheet_SelectionChange(ByVal Target As Range). Most cells of the worksheets are protected if the vba is executed. However, it was found that if user chooses not to run the macro when prompted, the worksheet can be edited whatever user want. So, how can the worksheet whole worksheet is protected if macro is not run?
I have just encountered a very peculiar problem when using Scrollbar_Change Event for Controls Scrollbar (in a worksheet). I have tree scrollbars and assigned Event code for each one: ScrollBar1_Change, ScrollBar2_Change and ScrollBar3_Change.
Events works perfectly when I click on the arrows to adjust the scrollbar. What is strage however, event is not executed when I adjust the scrollbar itself (pulling the bar with a mouse) if I try it first time after adjusting another scrollbar. In such situation Scrollbar is adjusted on the screen, even linked cell is changed, but the Change Event is not executed. But when the same scrollbar is adjusted second time Change event is executed. To sum up: Scrollbar_Change event is not executed when adjusting the scrollbar with the mouse the first time after "switching" from one scrollbar to another, but is works perfectly in any other situation. It has nothing to do with the code istelf. I get this effect when I create a new workbookm add 3 scrollbars and a code like:
Private Sub ScrollBar1_Change() MsgBox "ScrollBar1 changed!" End Sub
Private Sub ScrollBar2_Change() MsgBox "ScrollBar2 changed!" End Sub
Private Sub ScrollBar3_Change() MsgBox "ScrollBar3 changed!" End Sub
What may be the cause of this selective "disobedience"? Note also, that I get this error on Excel 2000 (not tested it on Excel XP or 2003).
I ran into a screen update problem in a project I was working on and couldn't find a solution, so I wrote some test code in a new workbook as follows;
Sub Flkr() '------------------------- 'Screen update test '------------------------- 'Select sheet1 Sheets("Sheet1").Select 'Disable screen update Application.ScreenUpdating = False 'Select sheet2 Sheets("Sheet2").Select 'display message MsgBox "Why is the %&$@ screen updating?", vbCritical, "??????" End Sub Seems simple enough,eh?
When I step through this code the first statement works(sheet1 is selected) The next statement is executed without an error, so I assume it works The next statement sheet2 is selected, and the screen updates!
I have a Form that user fill in information on, once they click the OK button, excel is filled with the inserted text on the form in the correct cells.
My question is, how do I validate that they have actually entered data in some cells, which I want to make mandatory, and if they have not, prompt them (this could be a simple message box) to fill it in. Let them fill it, and once they click OK again, check again... until all the mandatory fields are filled, only then will the macro fill in the excel cells.
I have a sheet with a country in it in A1 (validation list).
Depending on the country in A1 a country specific IF command has to be executed on a column1 with first cel = A3. This IF command also relies on the values in column 2 and 3 on the same row. (B3 and C3 are in the IF cmd)
I taught to do it like this : =IF($A$1="Spain";$A$5;0) with A5 being the country specific IF command which should be executed when spain is selected. however when I do this, this only works for the first cell ( I cannot drag this formule down, since it will always give the value calculated in column 2 and 3 on row1.
I'm not sure if the best way to 'select' the country specific IF cmd is with another IF command..
I have a 32 question test that I expect everyone to get most (if not all the answers correct. I have a cell that is counting the number of correct answers.
What I want to do is start a timer when the first correct answer is put in and stop when the last answer is put in. So essentially, I need a way of looking at a cell and starting a timer when it equals one and stopping when it hits 32.
I am currently working on code that uses either arrays or ranges. I often resize local array based on the dimensions of the input. However, I am running into trouble because arrays use ubound for dimensions, but ranges use ether rows.count/columns.count or ubound on value2. How can I determine whether a variable is an array or a range. the IsArray function happily passes a range variable, so that doesn't work.
I am using the formula below to return a blank cell if either logical test returns true but apparently the OR operator returns a #VALUE error when applied to a non numeric value in a cell - in this case cell E40. =IF(OR(E40="N",P40=""),"", SUM(P40,S40,V40,Y40,AB40,AE4))+F40. My goal is to return a blank cell if E40 contains the letter N or P40 is blank, otherwise perform SUM(P40,S40,V40,Y40,AB40,AE4))+F40.
I have a resolution calculator that I am working on.
You put your original resolution in say 1440x1080
then below you put in any one of the new target resolutions.
Place 720 in the height and you get a message that says
"960 is your new matching Width resolution"
Id like to follow up on that with an if statment that test to see if the cell is blank if it is then null, if not then test 960 in this case to see if 960/16 = a non decimal number.
So in the cell to the right of that sentence it would return the result "and 960 is 16 pixel safe" something like that.
I can probably figure out how I will handled the cell arrangement, numbers, and text I just for now need to find a way to do a test on whole numbers and return a text value (my guess is an if statement)
