Is it possible to trigger a forced comment if a particular value is entered in a cell?
For instance, if the letter "F" is entered into a cell, I want the comment box is pop up, is this possible?
And lastly... is it possible to automatically group comments? for instance if a row has 6 comments, I'd like to combine all of them into a comment box on the same row.
how to copy the contents of multiple comment boxes and paste in a single comment box.
The big picture is that I have a number of cells with numerical values in and text in comment boxes. I want to be able to click a button to copy the contents of the comment boxes and paste them, along with the numerical value from the cell, into a single comment box, ordered by highest to lowest value within the comment box, then delete the original cells and comments.
I am quite new to VBA but have been coping quite well so far with information of the web and analysing recorded macros.
My spreadsheet is very large & takes sometimes 5 to 10 seconds to recalculate. The problem is that is was wanting to recalc every time I edited a cell, which I do constantly all day long. Due to this, I have turned off automatic recalculation. Is there is a way I can use VBA to force just one cell or just one row to recalculate? Keep in mind that currently no cells recalculate until I hit F9 or go in & manually hit Calculate Sheet in tools > options > calculation.
View 6 Replies View Relatedhow i can force excel file to open in 1024*768 resolution?
View 4 Replies View Related I've looked through the threads and have seen some Save As posts, but nothing that seems to fit my task. All I want to do is force the Save As file type to .xls (for some reason on our network, xlsm causes save errors... After looking through various posts, I found this:
VB:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Not SaveAsUI Then
Exit Sub [code]....
The problem is this causes the Save As dialog box to show up twice... How I get the Save As dialog box to show up once?
I'm looking for a way to take a number (23.89145 or $3.45 for instance) and use a formula to force it to end in a 4 at two decimal places (23.84 or $3.44)...generally only interested in going down.
View 3 Replies View RelatedIs there any way to force some sort of garbage collect in order to free up memory? I am running some code which takes values from one workbook and pastes them into the control workbook. It currently works when 15 sheets of data are being moved however, my data has just grown to 16 sheets and now the code gives me the not enough resources message. If a garbage collect would solve this issue would I need to also reset any variables I had defined in the code?
View 9 Replies View RelatedI am concatenating data which will be displayed in a text box.
Is there any way to force a carriage return within the formula ?
I have an Excel sheet which serves as an input form. I would like to set something up where if the user inputs any value into the cell range B12:B100 then they are forced to also enter a value in column J of that same row.
Maybe something could be done where if they enter a value in B12, then they cannot enter a value in B13 before entering a value in cell J12?
I have created a tool in excel to facilitate different requests from our sales force. A drop down list in the first worksheet lists the request types. Once the type is selected the user clicks a command button that sends the user to the corresponding worksheet specific to the request type.
My problem is that I need to verify that the cells on the specific worksheet have all been completed. I tried to verify when saving in the workbook module, but I don't know how to only check the selected request worksheet.
One thought was to insert save command buttons on each worksheet and only allow saving through those buttons. (So I would need to remove any saving functionality from the file menu/toolbar.) Then I could attach the verification code to that button on each worksheet. Preventing saving until the requested data was entered.
If this methodology will work I need to know how to do the following:
1) remove alternative saving methods
2) Once those methods are removed, how do I code a command button to check that specific cells are not null and then "save as".
3) if cells are null then, stop save and show msgbox.
I am working in Excel 2003.
I have an excel sheet where the user enters a bunch of info. In one of the cells, they enter an "account code" - this is then used to SAVE AS the account code.
The problem... if they enter things into this cell that causes the filename to fail (eg: *, /), the code errors out. I can error handle this in VBA at the time of the save, but I would rather prevent them from entering anything that is not a letter or number in the first place.
I have a report which is produced by a macro, it works well. The report grows over the year to maybe 20 printable pages, landscape, and nicely readable.
Summary information is produced alongside the report over more columns than can be diplayed, so I have copied the summary data as a picture and pasted it below the main printout and adjusted the size of the "photo" to fit the page width.
I would like this photo to always appear on a new page of its own - not just tagged onto the pages before it.
I want to force the user to input a numeric value in a textbox and for a message to pop up if a letter is typed, what code do i use?
View 9 Replies View RelatedNeed to write VBA for inserting Footer for each page based on Print command. My object is whenever any one is taking the print from this file my specific footer should get printed. The user should not be allowed to change it.
View 4 Replies View RelatedProbably is in the wrong forum, let me ask a question to justify it being here. How can I force the y-axis min & max to be a particular number. If that isn't possible, how can I add a straight line to indicate the min & max?
View 2 Replies View RelatedI don't why the following code is not working. I am trying to force a date format when a user is trying to enter a date in anycell
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If IsDate(Target.Value) Then
Application.EnableEvents = False
new_date = Format(Target.Value, "dd/mm/yyyy")
Target.Value = new_date
'MsgBox new_date
Application.EnableEvents = True
End If
End Sub
want to specify page breaks and set print areas when running a macro. Appears to work sometimes, but then breaks (hard and soft) pop up on their own). Currently using something like this to specify a 2 page wide view...
ActiveSheet.PageSetup.PrintArea = "$A:$Z"
With ActiveSheet.PageSetup
.FitToPagesWide = 2
Set ActiveSheet.VPageBreaks(1).Location = Range("P1")
End With
Can I force breaks to be more absolute (more accurately)?
Is it possible to force an addin to be installed in Excel 2003 using VBA? I have a spreadsheet that uses the solver.xla addin in a vba macro. It works fine on my machine, where I have manually ticked SOLVER in Tools>Addins and manually added a reference. However, it does not work when I send to someone else. It seems that the addin is not being installed. I have VBA code that opens it:
On Error Resume Next
s = Application.LibraryPath & "solversolver.XLA"
Workbooks.Open filename:=s
but even with this, i get a "Unexpected internal error occurred, or available memory as exhausted" when I try to run the following
On Error Resume Next
ActiveSheet. Unprotect
Application.Run "Solver.xla!SolverReset"
Application.Run "Solver.xla!SolverOk", "$D$46", 2, "0", "$L$13:$L$14"
Application.Run "Solver.xla!SolverOptions", 100, 100, 0.000001, False, False, 2, 1, 1, 0.05, True
Application.Run "Solver.xla!SolverOk", "$D$46", 2, "0", "$L$13:$L$14"
Application.Run "Solver.xla!SolverSolve"
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios _
:=True
The one way to make it work is manually going into Tools>Addins and ticking it. This is confusing me as the above, along with the code that opens it, should not even require a reference, but it doesn't work without it
how to tackle this in excel vba using a macro. I want to force the first letter of each cell in Column A to uppercase until it reaches a blank
View 2 Replies View RelatedIs it possible to create a "floating" UserForm with VBA? That is, a form that is always on top af all the other windows. If not I think I will have to create an application in VB that imports data from the active Excel Spreadsheet, wich I have been lead to understand is fully possible. I really need this for the application that I am currently developing and I from what I have heard it is possible in VB.
View 4 Replies View RelatedI have input cells on a protected sheet with a tab order array setup. It was originally set up to work when their was a Worksheet Change. Not every cell is going to have data input into it. This is where the problem lies. If a cell is left blank it tabs out of sequence. I changed it to a Worksheet Open Sub but that didn't work.
Code is as follows: ...
I need to know the line or two of code to disable the
FILE and CLOSE buttons to force saving to the same file.
I know they exist, because I obtained and tested them in
an excel sheet, but somehow managed to lose the code
and the link from which I obtained them.
I have a UserForm that requires a value in TextBox10. I load a "1" into it at start up. If the user tries to delete the value in TextBox10 and exit, leaving either "" or 0 in the TextBox, it will cause a great many problems. I need someway to keep the focus in TextBox10.
I tried to find something using the search engine but I could not find anything searching with this subject.
Here is the code from my first attempt:
Private Sub TextBox10_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox10.Value = 0 Or TextBox10.Value = vbNullString Then
TextBox10.SetFocus
Exit Sub
End If
It doesn't seem to do anything. When I delete whatever is in the Textbox and hit enter, it should just stay in TextBox10 but instead it goes to the next tab location.
I have a TextBox in a Worksheet with a ScrollBar defined and its working perfectly! The only problem is that everytime I click outside the TextBox, the ScrollBar goes to the lowest position and I only can see the bottom of the text in the TextBox...
Is there any way of keeping the ScrollBar on its position everytime I click outside the TextBox?
Is there a way to renew / update the formula =NOW() without closing & reopening the file??
View 2 Replies View RelatedI am trying to use an If statement and wanting to evaluate another formula.
The formula is as follows: =IF(B6>=7,Table!M16,Table!M47) where B6 =REPLACE(D24,1,3,"") which returns an integer. In this case it is 6.
Unfortunately it doesn't process the formula value correctly in the If statement unless the reference cell is hard coded with the integer. Is there a way to force the formula to evaluate the value of the cell formaula first before doing the If comparison?
I have Excel file that is linked to Access Database. Everytime the user opens the file, there is pop up message that warns the user we there to enable the macro or to disable it. I want to force the user to enable the macro or even better if I can make disappear this warning and automatically enable the macro. See attached for more details.
View 3 Replies View Relatedis there any way to block users to select multiple cells in excel?
View 2 Replies View RelatedI'd like to import some dates into some textboxes on a userform.
I'm also going to let the user edit these, however... what i'd like is for the user to be forced to:
1) enter a date in the format dd/mm/yy
2) Enter a valid date (eg, not feb 30th)
I have a series of Userforms that I would like the user to be forced to enter in some sort of information in one of those userform in the series. I do not want it to be submitted blank. Is there a code or a properties toggle to make this possible.
Code:
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Sheets("MDF").Select
Range("RailcarNumber").Value = TextBox1.Value
[Code] .......