How do I programmatically freeze ( lock?) an autoshape so that it stays put even when the user scrolls up/or down?
FYI: I created an autoshape and assigned it a macro, thus it becomes a software button, I want the button to stay put.
I have a workbook with several sheets, but just on the sheet called “sales” I need a macro to do the following:
If F4 = CST
Then Lock down H4-S4 and AM4-AZ4
But if F4 is changed to something else I need H4-S4 and AM4-AZ4 to be unlocked
Is a password necessary for this? It is ou
I just want it to lock and unlock, not protect and unprotect.
Each sheet has the same basic formatting. A1 contains a name. B1, C1, D1 are column headers. B2:B is data. C2:C is data and always stops at the same row B2:B range does. The only differences between the sheets is that they might not stop at the same row. I want a macro that merges A1 vertically as shown in my spread sheet to the end of column B and C. I want a border around the merged data, as well as around the B data and the C data individually.
View 3 Replies View RelatedContext: I have a spreadsheet that contains a list of employee names and their certifications. I want to be able to assign a button to each employee in column B with a macro that is able to zip files from a folder that contains that employees name.
The following code assigns buttons to each employee in column B. At the moment the code I have is able to assign the macro "Zip" to each button.
[Code] .....
Now, this Zip macro is able to zip the file for the employee "Anthony Tran". However I need it to be able to recognise which employee's button I've clicked and search the same files as above except with that employees name instead of "Anthony Tran".
If it makes things easier, the code for creating buttons for each employee is able to name that button as the employees name that it represents.
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"
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
Context: I have a spreadsheet that contains a list of employee names and their certifications. I want to be able to assign a button to each employee in column B with a macro that is able to zip files from a folder that contains that employees name.
The following code assigns buttons to each employee in column B. At the moment the code I have is able to assign the macro "Zip" to each button.
VB:
[COLOR=#333333]Dim Btn As Button[/COLOR]
[COLOR=#333333]Dim rng As Range[/COLOR]
[COLOR=#333333]For I = 2 To RowCount + 1[/COLOR]
[COLOR=#333333]With Worksheets("Sheet1")[/COLOR]
[COLOR=#333333]Set rng = .Range("B" & I)[/COLOR]
[COLOR=#333333]Set Btn = .Buttons.Add(rng.Left, rng.Top, rng.Width, rng.Height)[/COLOR]
[Code]....
The following code is my Zip macro:
VB:
[COLOR=#333333]Sub Zip()[/COLOR]
[COLOR=#333333]Dim strDate As String, SavePath As String, sFName As String[/COLOR]
[COLOR=#333333]Dim oApp As Object, iCtr As Long, I As Integer[/COLOR]
[COLOR=#333333]Dim vArr, FileNameZip[/COLOR]
[COLOR=#333333]Dim FName() As Variant[/COLOR]
[Code]...
Now, this Zip macro is able to zip the file for the employee "Anthony Tran". However I need it to be able to recognise which employee's button I've clicked and search the same files as above except with that employees name instead of "Anthony Tran".
If it makes things easier, the code for creating buttons for each employee is able to name that button as the employees name that it represents.
I have a value showing up as #####. When I auotfill the cell to the next on the right, it shows the right value. How do I correct this so it just shows the right value in the first cell?
View 10 Replies View RelatedI've recorded a macro that copies an entire tab into a new spreadsheet then goes on the copy and paste information from one tab to another.
When I run the macro from the Tools>Macros menu it works perfectly.
But when I copy the code and add it to that of a button it fails and posts the following error: Run-time error '1001': Select method of Range class failed.
The first attachement shows the code for the macro as it is alone, and the second shows how I simply copied and pasted it into the 'view code' window of the button.
Needless to say I'm a beginner at macros and only every record them, I can usually make stuff work that way but this has me stumped!
I have a userform with a command button which fires a macro.
everything works fine so far.
my problem is:
I would like to add a msgbox at the end of the macro which confirmes "successfully completed".
I cannnot simply add the msgbox at the end of the macro. don't know what I'm doing wrong.
(see below)
Private Sub CommandButtonOK_Click().
If Me.OptionButton1.Value = True Then
ThisWorkbook.Worksheets("PropertyWorksheet").Range("A1").Value = "Government Securities"
ElseIf Me.OptionButton2.Value = True Then
ThisWorkbook.Worksheets("PropertyWorksheet").Range("A1").Value = "Corporate Bonds"
[Code].....
Unload Me
Application.ScreenUpdating = False
Sheets("MySheet1").Select
Application.ScreenUpdating = False
'run macro
MyMacro1 (adds, hides and deletes various sheets)
MyMacro2
[Code]....
Columns O,P and Q should show a "Y" if Column N has the percentage (the percentages shown at the top of those three columns) over the value in column D. I hope this makes sense!
******** ******************** ************************************************************************>Microsoft Excel - Team Lead Incentive.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutF3H3N3F4H4N4=
CDEFGHIJKLMNOPQ1***WF*Team*Lead*Incentive****As*of*4/5/07***(15%)(20%)(25%)2TeamBase*Team*****Hours******Wk*1Wk1*%Wk*2Wk2*%Wk*3Wk3*%Wk*4Wk4*%*Total$75*$100*$125*32600.25*592.00-1.37%623.753.92%*****2.54%***43388.00*467.5020.49%527.5035.95%*****56.44%***WF*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I have the word 'calculate' shown in the bottom grey bar of the screen. I think this may be affecting my Excel spreadsheet.
View 4 Replies View RelatedRefer to attached file.
I have below code which successfully create a macro button and assign the macro correctly.
This is only doing for one sheet and i need to modify the code so that it does for all sheets of the workbook.
[Code] ....
Test Macro_Botton.xlsm‎
I have a long complicated macro that processes a ton of data and gives the output in a new sheet. I then want to create new buttons (or some sort of user clickable triggers) on this new sheet that runs another macro.
I have got the actual adding of buttons in the sheet working, but I can't find a way to automatically assign macros to newly generated buttons during runtime. Is this even possible (or are there any clever tricks I could use to get around this?)
For example Cell A1 is 9.5 x 2.5 which gives 23.75
but what is actually displayed to users is rounded to 24
So how do I use 24 as a reference instead of 23.75?
So in Cell B1, it would be =A1x100 but the value is 2400 instead of 2375?
You can't use the ROUND function because if users decide to use 23.8 then the B1 cell needs to be 23.8x100
=IF((P3<$Q$1),E3-S3,IF((G3="B"),"Not Changed",IF((G3="MT"),"Not Changed","Not Changed")))
Above is the formula I'm trying to get to work but it's not-a-happenin. It seems like the first IF overides all of the others. Basically I want the first IF to be ignored if column G contains any of the words shown. So basically what I think is happening is that P3 is less than Q1 so it's ignoring the rest of the IFs.
Originally my formula worked fine as:
=IF(P4<$Q$1,E4-S4,"Not Changed")
But I realized that i needed a certain category of product ignored. Products are labeled in column G.
some Excel columns (see attachment). A1 is set to =C1 and the formula is dragged down, relatively referencing column C. Column B applies a formula to the values in Column A.
If I go into the formula bar for cells in A and push enter, the spreadsheet shows the correct value and I can then push enter in the adjacent Column B cell to solve. But, how can I get these data to display properly automatically?
I have a sub that is called when the user clicks a button on the form.
The sub takes a few seconds to run, so I have created a userform which contains a single label, which simply says "working ... please wait" in large letters.
A code example is as follows:
[Code] ....
Seems simple enough, but what actually happens is that the PleaseWait userform is displayed, but contains nothing (i.e. blank white - the contents is not being rendered). I'm guessing that I need to call something on the userform to force it to render its contents after the modeless call to show it. How can I do that?
I am trying to re-jig a formula that shows hours worked for a particular
day, to showing any absence detail shown for a particular employee. I have
the fllowing formula which doesn't work and I'm not sure why. A similiar
formula works for me getting basic hours worked etc
=SUMPRODUCT(--(DailyDate=F67)*(StaffNumber=$C$11),(AbsenceDetail))
My range names are correct and all have the same 'length'. F67 is 09/05/05;
StaffNumber = 100 and the value that is within the AbsenceDetail cell for
the 09/05/05 for # 100 is Holiay, except my formula returns 0 (I have it
formatted as General)
I have the results of a "IF" formula. I want that number to be shown as currency and allow it to be part of a "sum" formal. Is this possible?
View 2 Replies View RelatedI'm trying to plot a function with specific values marked on the X axis (or at least distributed according to the said values). Namely, I have something like this:
And while it does plot the Y's as I want them to be plotted, X'es are just used as labels, which causes 0 and 100 to be equally apart as 933,9 and 944. How can I plot it, then, so that I simply select my desired Y values, their corresponding X values and get the plotted function?
how to make a formula to only display the results shown when using the filter..........
View 2 Replies View RelatedI have an Access program that acts as a dashboard to open Excel reports. One of the Excel workbooks opens a form in the Auto_Open routine. My problem is that control is never passed back to access after the form opens. It appears that the code stops executing until the userform is closed. How can I open the excel workbook, show the Excel userform, and continue processing my Access code?
Set XL = Excel.Application
With XL
.Workbooks.Open FileName:=FileName, ReadOnly:=True
. ActiveWorkbook.RunAutoMacros xlAutoOpen
End With
XL.Visible = True
' Excel Code
Sub Auto_Open()
Unload UserForm1
UserForm1.Show
' The code get "stuck" right here
End Sub
I'm writing an IF statement referring to a cell that contains the character "C" and comparing it to a blank cell, and it's returning True, while it should be returning false because they're not the same.
View 6 Replies View Relatedi have uploaded a file.....basically i have an input of names and i want to repeat these as shown in the output..is this possible.
View 5 Replies View Relatedhow I can have the selected item in a dropdownlist also to be shown in another cell? Typing the cell number of the dropdownlist in another cell (f.e. ="dropdownlist cell number)" doesn't seem to do it.
View 2 Replies View RelatedI'm using the code below to form part of staff resource spreadsheet, but I'm having a little difficulty with a piece of the script.
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rInt As Range, res As Variant
Dim cell As Range
Sheets("Input").Protect "password", UserInterFaceOnly:=True, AllowFiltering:=True
[Code] ....
The piece of the script which I'm having difficulty with is this:
Code:
For Each cell In Range("B7:B400")
If cell.Value = "No" Then
MsgBox "If " & cell.Offset(0, 1).Value & " has left R&D, please remember to delete any future resource forecasts"
End If
Next
When the cell value has changed to 'No' the pop up message appears, but for some strange reason the message is shown 5 times, despite their being only one record.The other problem I have is if I add another record and change the value to 'No' in column B, the message is shown twice, once for the previous record and the second for the new.
I'm working on a project where column A will always have numbers added every month. How can I get column B1 to automatically retrieve the last number shown?
Example
Month 1 Month 2
Column A Column A
1254 1254
5871 5871
5654 5654
5301
5024
I'm making a line diagram, showing how a value changes over time. My data is measured every 30 min, which results in a mess when it's plotted over a month.
How do I limit the number of times shown on the x-axis, for instance so that only the date (every 24 hours) is shown?
i have the following kind of table
Raw Material ReportDateDetailOrder Given Received Opening Balance25-Oct-07order to Johson50029-Oct-07Receive from jonson10014-Jan-08Receive from jonson 5405Pending sum500505-5
as you can see that the pending order (receivable from johson) is -5 as we reveive more then order given.
i want here that when we receive more than order as in this table the pending must be shown 0.
i can do it by apply the condition when pending is
Can you increase the number of fields shown in the list so that you don't have to scroll through the entire list?
View 2 Replies View Related