I have a small sub routine below I would like to make a slight modification to. The routine currently references formatting relative to the Offset statement in bold below. Instead, I would like the formatting to come from a cell address listed in a cell just to the left of the cursor when the statement is encountered.
View 2 RepliesI would like to protect the worksheet from modification by other users except column A ( suppose A1: A15).
I have been doing this by removing Locked on format cell and then Protect Sheet on Review tab.
Is it the right way or there are other more reliable way?
I have the following forumla: =SUMPRODUCT(($C$13:$F$74>0)*1)
I need this formula to compute only if B13:B74=A4. I am not too sure how to add this in.
I did modify some codes inside my addin, then did save the modified addin file by pressing the save icon from the VBA window..........but i found that the addin file was saved without the modification made to it. so i lost all modification did to the file. i donot know what i did wrong ?
View 2 Replies View RelatedI have been modifying the options available on my right click menu, getting rid of about half of what was there and adding items I use quite often like Paste Values, Paste Formats, Paste Formulas, Sort..., AutoSum, and Format as Percent, as well as adding group dividers. I have it just the way I want it, but I have one issue. If the clipboard is empty, all of the paste buttons are disabled on the Standard toolbar and in the Edit menu as are the Paste and Paste Special... items on the right click menu, but the new paste items I added to my right click menu are still enabled. If I click on one, I get a "PasteSpecial method of Range class failed" error because I have no range selected. I got around this by adding On Error Resume Next to the macros of my added paste functions - not elegant, but functional. Is there a way to have those items appear grayed out and disabled on my right click menu when the clipboard is empty as opposed to my current workaround?
View 9 Replies View RelatedI have an Excel template with 10 Worksheet(tabs). 8 of the sheets have predefined charts built based on the data in first 2 sheets. Through a web application user downloads data on the first 2 sheets. A macro is then executed (at the end of the download) to Update (through Macro) the charts in the remaining sheets and the sheets are password protected. The idea here is that user can generate predefined charts from the downloaded data but cannot modify any information. In addition to that user need an ability to add additional sheets to generate custom reports/charts.
So my requirement is to protect the 10 predefined sheets from modification including the modification of sheet name/deletion of sheet but allow user to add additional sheets. Protecting the workbook does not allow user to add additional sheets. Protecting individual sheets doesn't prevent user from renaming the sheet names.
Search Excel Database - Modification ...
View 9 Replies View RelatedI'm using this formula in a Conditional Format rule.
=CELL("row")=ROW()
Can anyone help me modify it, so that only the active cell is formatted instead of the entire row?
way to save user-settings at runtime, preferably in the same or another module. For example: how can I save a bundle of variables such that the next time excel is started and the workbook is opened, the user can continue to work with his personal settings?
Background:
Using the macro, I am writing on atm, a user will be able to adjust the macro according to his input-samples. Here the columns and rows of an input can be varied to fit the macros requirements. It would be great if those settings could be stored, irrespectively of where the file currently resides. That is, best it would be, if the source-code itself could be changed ... maybe by adding another module?
i was able to setup a email code, which will automatically extract the unique row reference data and sends the email. However, i am struggling to add couple of things, i searched most of the web portals .. wasn't lucky.
1. while email drafting i need to use the data from Column B onwards
2. i need to insert the default email signature during email draft ...
I have a macro that I found on the net for copying a filtered selection and copying to a new sheet. I would like to alter it slightly so that it just clears the sheet called 'Interval tasks' instead of deleting it and creating a new one.
View 8 Replies View RelatedI don't know if a macro is necessary, but the built in Insert -> Insert copied cells -> Shift cells down function can't accomplish what I require.
I've pasted in two example tables for what I'd like to do. For the first table, the data in column C is a name and the cells from columns D-Z, rows 7-10 is what I want to copy and paste from row 12, column D; row 13, column D; etc, etc. However, I want the full rows below the name in column C to move down when I paste from column D. Using the Insert function on the Insert blade only gives me the data in columns D-Z moving down.
The second pasted table gives an example of how I want it to look.
A
B
C
D
E
F
G
H
I
[code].....
it's possible to set a "key" for a vlookup so that one of the parameters of that key can be any value? For example if the data set key used in the lookup contains a concatenation of 4 parameters (a_b_c_d), is there anyway to modify the lookup array key so that the "c" parameter can be anything, ie. something like a_b_*_d ?
View 3 Replies View RelatedI'm using a script that when you double click on a cell containing Data Validation > List it changes to a combo box that has an autocomplete/increased columns view...It's working great, however there are two small modifications I would love to do and am not having much success with. Currently, you double click the cell w/the Data Validation > List in it, it then changes to a combo box and you can auto complete using the keyboard or select the dropdown and choose from an increased list. However, I would like to be able to simply single click on the cell, have it change to the combo box and auto expand the list if possible.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsList As Worksheet
Set ws = ActiveSheet
Set wsList = Sheets("Rep Assist Report")
Cancel = True
Set cboTemp = ws.OLEObjects("RepName1")
On Error Resume Next
With cboTemp
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error Goto errHandler
If Target.Validation.Type = 3 Then.....................
We are using Excel 2007.
I wanted to add the date of the last modification of the file to the printed pages, so I googled how to do it and found a useful answer on this from from 10 years ago. It said to press ALT+F11, then on the left side go under VBAProject, there go to ThisWorkbook and there paste the next code:
Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
For Each wk In Worksheets
With ActiveSheet.PageSetup
.LeftHeader = "Last Modified on " & ActiveWorkbook.BuiltinDocumentProperties.Item("Last Save Time")
.CenterHeader = ""
[Code]....
Then I saved it and closed. Like I said, it worked perfectly.
The problem is that when I open the file after closing it, it does not appear, and I have to paste the code again every time I open it. Not only is this not comfortable, it also counts as a modification, so even if I haven't changed the work sheets themselves, just wanted to open and print it, it shows today's date.
My question is, who to I save it permanently so it will remain always after the first time I created it?
While we're at it, if there's a way for that code to appear on every NEW Excel file I create automatically
In sub which will convert file to PDF and save it to assigned folder and then attach it to email in outlook. All works fine.
However is is possible to modify the code to save fole to respective month folder ( as per current date and year)?
e.g. I have created folder Named 'Trial' in C drive . This has sub folders 2014, 2015, 2016, 2017, 2018 Each year folder will have sub-sub folders month wise like This has sub folders as Jan , Feb , Mar , Apr .... till Dec Now e.g. if date when the pdf was created is 23/4/14 then it is saved in C:Trials2014Apr automatically. Currently every year I keep creating new folders etc... bit primitive though.
VBA jumps out of my called subroutine which is stored in a Module halfway.
When I call the SortLec() sub, it runs until y1 = Application.Match(MC & "-" & CI, rng, 0), and then it just jumps back to the SUB commandbutton1_click() IF statement. Why is it that VBA skips the rest of the code from my sub?
Option Explicit
Private Sub CommandButton1_Click()
Dim i As Integer
Dim numofrows As Integer
Dim workbook_directory As String
Dim file_name1 As String
[Code] ............
Im trying to call another subroutine within the same workbook but it doesnt work,
---------------------
Sub Macro1()
Dim mac1 As Variant
Dim mysub As Variant
Select Case Cells(1, 3)
Case mac1
Call mysub
End Select
End Sub
---------------------
in writing a subroutine that needs to ask the user
1) tax file number
2) Income
3) tax withheld
It then needs to use a function that i already made called 'incometax' that calculates 'taxpayable' and use that to calculate taxreturn (difference between 'taxpayable' and 'taxwithheld').
I then need it to output like this in a message box
Estimate for tax file number: .....
Created on ......
Total income: ......
Tax witheld: .......
Tax payable: ......Tax return: .......
Is calling a subroutine within another different than running it from the play button in the VB editor? I am getting different results.
I have the following code that I want to call from another subroutine that creates a series of command buttons.
Sub DynamicButtons()
Dim ButtonCount As Integer
Dim ctl As OLEObject
' Create the Button objects
ButtonCount = 0
For Each ctl In Sheets("Sheet1").OLEObjects .............
Is it possible to refer to an object which is in another subroutine?
Or should i have to give its definition in each sub i need it?
I need to allocate an existing subroutine (its code below) to a new button I'd like to add to my sheet. Two questions arise: to use "form control" or "activeX"?, also should I redfine the first line of the code?
[Code] .........
I want to pass the name of the routine as a parameter.
View 6 Replies View RelatedMy code passes a string created by a function to procedure. It all works great, but i need to add something so that if the string = false then sub doesn't run. What's the best way to do that. Here is some of the
View 6 Replies View RelatedI met problem in calling into a subroutine. It is error 424. I have dim all variable. However I still get it.
Code:
Sub UpdateAll()
'
' UpdateAll Macro
[Code]....
I got the error when I pressed F8 to step into the getOneRecord() function, which is highlighted in red.
My code defines a variable: newrow. Then I call a subroutine using the Call command.
The subroutine does not recognize newrow.
It says it has value zero. How can I pass the value of newrow on to the subroutine.
I have 60 checkboxes on my spreadsheet and basically, when any of the checkboxes are clicked on, the same action should be performed.
The only way I know how to do this is if I were to write the same code 60 times... one for each checkbox_click() method.
1. Can a Subroutine be called from within a user-defined Public Function? How?
2. Is there a difference between calling a subroutine with a 'Call Sub_Name' statement, vs. calling the subroutine with an 'Application.Run'(?? or similar) statement?
I have a short subroutine to delete a row in a second worksheet (SUMMARY EXPENSES) if one is deleted in the main worksheet (AS CODES) as per below:
[Code] .......
In theory the row deletion should only be triggered IF dynamiccounter is smaller than staticcounter, but the fact is anything whatsoever triggers the row deletion. If I type text into a cell a row gets deleted. If I copy a cell, a row gets deleted. It works great at deleting the line, but I suspect it has nothing to do with the counters I set up and the reassigning of the counter value towards the end of the If statement.
I'm trying to put some visual basic out on the network drive at work so I created a book and called it Macro.xls. I then saved the code within that book and saved it out on my network. I'm now trying to run that code by calling the sub and don't know how to do that.
View 7 Replies View Related