I have a command button on worksheet2 of my spreadsheet, which has a macro assigned that will take the user to worksheet1 when it is clicked.
My problem is that when I hide worksheet1 using xlSheetVeryHidden - the maro seems to die and the command button no longer works.
I have a command button on my form that unloads it when i am finished. But i have to press it 20 times before it closes. The only line i have in the code is "UnLoad Me"
View 7 Replies View RelatedI have made a list box and can see the data in it and make multiple choices. What I need to know how to do is put a small button and make the list box appear and make the selected choices, then hide the list box but the button visiable. I would to have check boxes and a scroll bar in the list box by the names but i am stumped. If you could show the code that will make this work that will be great!!! lets say cell B3 to B6 shows cat, dog, mouse, bird. I would check the box and choose cat and bird and have only these 2 show up in the cell B2 separated by a comma. So each line for about 100 lines down will need to have the same choices made but with equipment names, people ect. or is there something better that will work. I run Excel 2003 and windows xp.
View 9 Replies View RelatedI am using Excel 2010. I am using a shared workbook that was originally created in Excel 2003. This workbook has a command button that is supposed to be clicked to open a userform. However, I cannot even click on the button when opening the shared form in Excel 2010. When the form is not shared, the button works fine.
I have no problems with the button when opening the shared form in 2003 or 2007.
Is there anything I can do to make the command button work while the form is shared and while using Excel 2010?
I have this macro which sits in the workbook module:
Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldRng As Range
On Error Resume Next
If Application.CutCopyMode = xlCopy Or Application.CutCopyMode = xlCut Then
OldRng.EntireColumn.FormatConditions(1).Delete
OldRng.EntireRow.FormatConditions(1).Delete
[Code] .......
It's used to highlight the selected/active row.
But, as soon as I run another macro, it simply stops working entirely and I'm left with a row permanently highlighted until you manually remove the conditional formatting. All formats and cell colours controlled in the other macro stop working as well. This is the other (edited for publishing) macro, which is used to send an email, and is found in a module:
Code:
Sub Send_Mail()
'this sends an email that sends a text message
Dim OutApp As Object
Dim OutMail As Object
Dim MyButton As String
Dim strTo As String
[Code] .......
I didn't write the first macro so I'm not sure where the problem is? I'm guessing there is something in the code that is stopping it from working (i.e. running conditional formats) as soon as another macro is run? Is that was is happening?
i have a worksheet named for example 'allocation 1' this is a master document and is opened and modified and 'saved as' under a customer name. This then stops a few important macros working properly because they refer to the original title and not the new saved title. Is there any code that will let the macro recognise any new title it is saved under?
View 10 Replies View RelatedI'm using Excel 2007 and my s/s is 360000 rows deep.
To cut+paste formulas+formats from one column to another I'm using the following
Sub move_formula_and_formats_from_I_to_L()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Range("I1", Cells(Rows.Count, "I").End(xlUp))
With cell
If .HasFormula And Not .Offset(1).HasFormula Then
.Cut Destination:=.Offset(1, 3)
End If
End With
Next cell
Application.ScreenUpdating = True
End Sub
The code stops working after 159000 rows and highlights (in yellow) the line:
.Cut Destination:=.Offset(1, 3)
I have joined out of desperation relating to a problem with an Active X control inserted into a userform. This control accesses and returns data from an instrument on the RS232 PC comms port. All works well until I try to save, copy or print the workbook using usual VBA code, then mysteriously the code just stops, and Excel needs to be restarted again. It seems to just get lost!
View 2 Replies View RelatedI was given the following formula by Domenic some time ago that I use along with VBA code supplied by Fin Fan Foom to open do a lookup on a closed workbook:
=If(ISNUMBER(MATCH(MIN(If( Date=D2,If(ABS(Time-E2)<"0:30:30"+0,ABS(Time-E2)))),If(Date=D2,ABS(Time-E2)),0)),INDEX(Contact,MATCH(MIN(If(Date=D2,If(ABS(Time-E2)<"0:30:30"+0,ABS(Time-E2)))),If(Date=D2,ABS(Time-E2)),0)),"")
Everything works fine, until I attempt to…
- cut & paste a block of cells, or
- do a SaveAs, or
- change a worksheet name
The formula will no longer function after performing any of these tasks (the cells go blank), and I have to close the workbook without saving to preserve the original workbook functionality. All other tasks, including even importing of different files into the workbook have NO negative effect.
The formula uses an “INDIRECT” named range. Sheet name changes are reflected in the named ranges, but any change of names somehow causes a malfunction (no sheet names are referenced in the VBA).
I thought that the large lookup workbook (40,000 rows) may be a problem, but even after deleting most of the data and retaining only a few rows, the problem still remained.
After the formula cuts out (after performing the above tasks), if I open up the lookup file, then the lookup will work again, but if I close the lookup file, it will stop working.
I doubt the problem is in the VBA code since IT WORKS, and besides, I have other simple VLookups formulas that also uses the same process (and VBA code) and they continue to work just fine when that one formula gives out. The only thing I can think of is that the complexity of that one formula may be an issue.
Anyway, I the original thread is long, so I’m starting a new one here. The original thread is here (Domenic’s formula, pg 7; FFF’s code, pg 8):
Dedicated Cell To Choose Lookup Table
I have a conditional formula that highlights dates red when it meets a certain criteria. The file named MS Working, the conditional formatting formula (formula below) works as it should with no issues. The other file MS NOT Working, the conditional formatting formula (formula below) has stopped working as it should. What I did? I inserted a new column to the left. Where the MS Working file has only 1 column to the far left, the MS NOT Working file now has 2 columns to the far left.
It seems as if the formula adjusted itself when I inserted the new column; however, its not working.
MS Working
Conditional Formula: =AND(TODAY()>B3,NOT(ISODD(COLUMN())),B3<>"",OR(C3="",C3=0),B3<>0)
MS NOT Working
Conditional Formula: =AND(TODAY()>C3,NOT(ISODD(COLUMN())),C3<>"",OR(D3="",D3=0),C3<>0)
Excel 2013
Our company has a shipping report spreadsheet that has the job number in column A and the ship date in column F. I want to track other things on a separate spreadsheet. So far, we are only down to row 1440 so in order to account for future jobs, my code on the other spreadsheet is:
=VLOOKUP(A2, '[Shipping Report.xlsx]Monthly shipping schedule'!$A$1:$F$5000, 6, FALSE)
Also, the jobs are arranged by ship date, not by number order.
This has worked perfectly for months but for some reason, it will no longer recognize job numbers greater than 1331. The format is the same between the working and non-working cell. Is there a max limit for vlookups that I am not aware of? If this was a sudden failure, I could nail it down but there has to be an underlying reason I'm not seeing.
Here is a sample of my spreadsheet:
Ship Date
1325 12/27/13
1326 3/10/14
1327 1/4/14
1328 1/31/14
1329 3/3/14
1330 1/22/14
1331 1/15/14
1332 #N/A
1333 #N/A
1334 #N/A
1335 #N/A
1336 #N/A
1337 #N/A
1338 #N/A
1339 #N/A
1340 #N/A
I am using a BeforeDoubleClick event to look for a worksheet name and if it exists, go to the sheet. If the worksheet does not exist, it is creates a new sheet by copying a hidden sheet and naming it using a name in a cell reference. It works for the most part, but I am finding that it is having an intermittant issue. Sometimes, when I double-click, it double-clicks the target cell for editing, and then it renames the active sheet to the cell reference rather than copying the hidden sheet. It seems to happen when I reach 35 sheets created, but not always. Here is the
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim WSname As String
Dim WScheck As Worksheet
Dim WScheckname As String
If Not Intersect(Target, Range("A5:L85")) Is Nothing Then
WSname = Range("L" & Target.Row)
On Error Resume Next
Set WScheck = Sheets(WSname)
If WScheck Is Nothing Then 'Doesn't exist so create it
Sheets("Master_SLP").Copy Before:=Sheets(Worksheets.Count)
ActiveSheet.Name = WSname.........................
Is it possible that when you press a command button, that the first thing it does is to execute the code assigned to another command button (IE in another sub).
View 9 Replies View RelatedI have a simple list of data (Name, Room, etc.) in a spreadsheet. Each day I click on the spreadsheet tab and create a "copy" and (move to end). This creates a spreadsheet for the next day's data.
My problem is that the macro I use to sort this data never works in the newly created spreadsheet within the same workbook. As usual, I'm sure it is something relatively simple that I am overlooking.
How can I return name of a command button on click? I want to create some sort buttons on a sheet I will use regularly to speed things up. I thought if I could call each sort button by the column letter the button sits in I can use this in a single sub for all buttons
e.g the button sitting in column A is called "A". when i click the button, it returns it's name to a variable which I can then use to sort column A. I know I could create a seperate routine for each button but I was just trying to think of something neater.
I have a userform with a button on it named But1. Is it possible to store But1 into a variable then activate the But1_Click action using a variable?
Dim strButname As String
strButname ="But1"
Now, how would you activate the But1_Click method using strButname variable. With listboxes you can use the Control(strListboxName) methodology?
This doesn't work, but gives you an idea of what I am trying to do.
Controls(strButname)_click
I currently am using a form button to create new rows.
I need two different kind of buttons. I have a button that does this-
VB:
Sub InsertRow()
'this inserts a row where the button is clicked.
Dim r As Range
[Code]....
However, when I try and create these codes using a Command button (I need it for the color), I get the error
"unable to get the buttons property of the Worksheet class"
P.S one other thing, say the button is in row 3, and Cells A1 and A2 are merged, when you click the button in row 3, it should extend the merge to A3.
i want to create a save button in sheet 1, on clicking the same the data entered in the particluar cells of sheet 1 should get saved in sheet 2 in given format
View 1 Replies View RelatedThis 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"
ActiveSheet.Select
Range("B4:B37").Select
Selection.ClearContents
Range("G4:G37").Select
Selection.ClearContents
Range("B1").Select
Selection.ClearContents
Range("D1").Select
Selection.ClearContents
Range("F1").Select
Selection.ClearContents
Range("J1").Select
Selection.ClearContents
Range("M2:M3").Select
Selection.ClearContents
Range("B4").Select
ActiveWorkbook.Save
Application.Quit
I have read several articles saying using a command button as a toggle button can't be done but some articles have said it is possible using the state properties.
.State = msobuttondown
.State = msobuttonup
Is there any way to combine this with the onaction property so that when you press the button, it stays down and activates a macro then when you press it again it pops back up and activates another macro.
i try run macro with commandbutton, but it doesnt work. I insert button from "control toolbox" then i create a macro. Then i wrote this macro to "Private Sub" but it still doesnt work.
When i click on button, it only open editing macro, or select the button.
I'm very much in a bit of a pickle. I have updated my operating system from vista to windows 7 and a macro no longer works. The old code is
View 4 Replies View RelatedSome very basic
Cells(293, 1).Select
Nothing complicated at all in that. Yet if I put it in the code for a particular Excel sheet, it doesn't always work.
Same with
Range("A293").Select
(Yet wierdly, to confuse matters, very occasionally it does work)
If I put exactly the same code in Procedure 1 in Module 1 and call Procedure 1, I never get an error, and the code executes absolutely fine.
But I have to keep setting up procedures and referencing them for, quite frankly, code which should work no problem.
I have searched this topic but I came up with nothing regarding my exact situation.
I have 5 buttons on sheet 1. I want them to all direct me to another sheet in the same workbook. However, some buttons will contain more than one sheet.
Example: Button 1 will direct me to 4 different sheets. Is this possible?
Also, When I open the workbook, I see many sheets that I am working from but I do not want them to show up when I open the workbook. The only way to see these sheets is to click on the command buttons at the top of sheet 1.
When I press a command button I want to:
Uncheck a checkbox made with Control Toolbox
Check a particular Option Button within a group box made with the Forms Toolbar.
EDIT: Clarification, I want both things to happen when I click one button.
Is it possible to check multiple checkboxes with a command button?
For example, I have 20 checkboxes (C5 to C25) when command button is clicked
C5, C12, C17, C20 would be checked.
how to keep a command button in a certain location even if the a row or Column is Deleted or Inserted into the worksheet?
View 1 Replies View RelatedI know that this may seem a little strange to request as you can just select the row and delete it, however the sheet is designed as "idiot proof", so I would like to implement this.
Basically, I have a box which states "If you need to delete a row due to incorrect entry, please type the row number here (next cell), and click delete".
I want to know if by using VBA, you can reference the number entered in the cell, and use this to determin the row in which we are required to delete.
I have a 2 worksheets. One "Projects in Process"and the other "Project Completed". when the users fill in the information in cells A4-F4 (line 4 is the starting line and it could go down as far as line 23 or a23 - f23) In Projects in Process they press a "completed Project" command button and the data in a4-f4 copies over to worksheet "Projects completed". In the next available row (starting at row 4).
View 13 Replies View RelatedI am running Excel 2003 and in my workbook I created a macro and assigned it to a toolbar comman button, "Copy Filtered Data". This macro copies the visible data into a new workbook.
This works well but others in my company will also be accessing this workbook from a sharepoint and I discovered that when they open it this command button is not on their toolbar.
Would anyone know what I need to do to have this button load on the toolbar when the workbook opens?
Also, I would like this button to either unload or greyout when the workbook that this button is intended for is closed.
