I have an expanding worksheet and would like to move a button so that it is always near the bottom of the worksheet data. When the button is pressed it reworks the worksheet and adds in some additional rows (up to 10 max). I'd like it so that the procedure also moves the button down keeping it in line with approximately the last row of data but not sure how to do this with vba...
View 4 RepliesWhat is the easiest way to have a button added into a certain cell? Basically I have my workbook enter columns into a certain sheet. When a column is added into that sheet, I want to also add a button, that gives the option to delete the column that it is sitting in. I tried to record macros to figure it out, but I didnt have any luck figuring out logic behind the button placement.
View 7 Replies View RelatedIs it possible to place a control button on a cell?
View 1 Replies View RelatedMacro which will add a Listbox (Form control - not ActiveX) on each worksheet and give a sequential name to each ListBox.
View 5 Replies View RelatedI'm trying to insert a worksheet change event using VBA. I have this sample code from here -
http://www.cpearson.com/Excel/vbe.aspx
Sub CreateEventProcedure()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Const DQUOTE = """" ' one " character
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("ThisWorkbook")
Set CodeMod = VBComp.CodeModule...................
I created a macro to create command buttons on sheet 1 of my worksheet. The created buttons using oleobject and class type: forms.commandbutton1, creates an object with no properties on the sheet (i.e. if you right click on it excel thinks it's an object and not a control.) Is there a way to create it as a control?
View 4 Replies View RelatedPart of my code adds a Worksheet to the selected Workbook and then re-orders them alphabetically, but one of the Worksheets (Called home) should always remain at position 1. Is there a way to dictate exactly which postion it should be in so that I can place it there after the sort is complete?
View 7 Replies View RelatedI have rankings from several different sources all with different rankings for different people with a different number of people in each set of rankings.
I would like to match the names from each ranking set and place the corresponding ranking into one worksheet. I have done this without the code but over the course of the next couple of months the rankings will be changing.
I have them importing from the web so when they update I would also like for my rankings to update. Attached is the rankings each in their own worksheet.
The final sheet is what the end product looks like.
I have a simple one-column section (column A) of values in Sheet1:
Column-A
Hal
Sonny
Betty
Adam
James
I would like to sort this, but have the sorted results displayed in Column-A of Sheet2.
How can I do this?
Note: I need to be able build flexibility into this such that I can add names to the bottom of the list in Sheet1, knowing that the results in Sheet2 will be able to accomodate the additions.
I want to find a way to create multiple worksheets and matching data to be placed on appropriate sheets.
Here are more details (Please check the attached sheet screenshot as well):
Excel_Macro_Requirement.jpg
In a workbook, there is a "Master-Sheet". This master sheet contains 8 columns.
I want to create as many new worksheets after master sheet as the values are there in column B (Column 2 i.e. Ad Group). In above attached screenshot, there are 8 values (B2:B9 or A-H). So, I would like to create 8 new worksheets after the master sheet. Also, I want to rename them based on their value from Ad Group column.
Each newly created worksheet should have same columns as the master sheet . Same 8 columns with their name intact.
Finally, I want all matching data of the Ad Group values to be placed on their respective newly created worksheet. For example, worksheet A should have A2:H2 data. Worksheet B should have A3:H3 data, and so on.
Please note that same ad group may have more than one row data. But I don't want to create multiple worksheet of same name ad group. The worksheet should be just one, but all matching data should be placed in that one sheet.
I know it is a bit complex task, but I am sure there would be a way to perform this automatically - probably a macro.
i'm using excel 2003 SP2. I have several sheets with the same buttons. And i have a final sheet where I collect a summary from every sheet. I want to create in my summary sheet a button that runs all the buttons from the other sheets, so i can get all the info.
View 3 Replies View RelatedProgrammatically UpdateNamed Range. Is it possibly to update a named range via VBA?
View 2 Replies View RelatedThe coding of the actual changes in the different modules is performing fine ...
But, I am stuck .. with two instructions : how to remove Add In password, and, how to set its IsAddIn property to false .
Until recently I successfully used this code to navigate to a web page and populate some of it's boxes, before programatically pressing the page's "Validate" button, whereupon the page would test the data I'd populated the boxes with, and come back with an answer to tell me if there were any errors in my data:
Private Sub CommandButton1_Click()
Dim uf1_cbutt1_click_ie As Object
Set uf1_cbutt1_click_ie = CreateObject("InternetExplorer.Application")
Can anyone shed any light on how I may be able to programatically navigate with VBA to the page I want? I'm also having trouble finding the new name of the "Validate" button - and how to programatically click it.
I have the following function that looks at a range of data and deletes duplicate entries in each column. I want to change the range that is examined programmatically, rather than having to retype it for each range in this huge sheet! I have subtotaled and counted the rows in each range, so I know how many there are, but I'm stumped as to how to increment teh range correctly. I'm attaching a small sample of my data. The full sheet is 30,000+ rows and 94 columns.
Sub DelDups2()
Dim rngSrc As Range
Dim NumRows As Integer
Dim NumCols As Integer
Dim ThisRow As Integer
Dim ThatRow As Integer
Dim ThisCol As Integer
Dim J As Integer, K As Integer, x As Integer
Set rngSrc = ActiveSheet.Range("c68", "cr70")
NumCols = 94
NumRows = rngSrc.Rows.Count
ThisRow = rngSrc.Row
ThatRow = ThisRow + NumRows - 1
ThisCol = rngSrc.Column
For x = 1 To NumCols
For J = ThisRow To (ThatRow - 1)
If Cells(J, ThisCol) > "" Then................
I have a VBA procedure that I need to be able to link/attach to an excel file which is created on the fly, and I need to do this programatically / on the fly. Basically, currently from my front end app, I output the necessary data to excel, and then format that data in excel using vba code sent through OLE to excel. Right after the formatting is done, I would like to then send the necessary vba commands to excel that would take my existing VBA sub routine, link it somehow to my excel file (by inserting it as a macro?, by registering/linking it as a .bas file?, any other options/ideas?), and then give the user a button or shortcut in the excel file that will run that VBA sub routine.
The underlined part above is my main goal here, and I already have the rest covered. I know there is more than one solution for this, but I would prefer a solution that allows me to store the vba code in the excel file itself, so that I only need to distribute the .xls file whenever I want to deploy this spreadsheet somewhere new.
Is it possible to lock/unlock the VBA code in a workbook through VBA from another workbook? I wish to copy modules from one workbook to another, both workbooks will have the VBA code password protected to stop prying eyes. Obviously I need to unlock the VBA in the target workbook before the copy and lock it again afterwards. Can this be done programatically? If there is no function in VBA to do this can you provide an example of how to do it using SendKeys if that is the only way to do it?
View 3 Replies View RelatedRunning into this issue of "transworksheet macros"
I have a master "update" commandbutton on sheet 1. I have two sub "update" buttons on sheet 2 and 3 respectively.
What I would like to do is have the user click the master button, that would "click" the two sub buttons so they run their respective macros on their respective worksheets (within the same worksheet.
I have tried pasting the macro code in the master button, telling it to select the sub worksheet and then run the macro, but the marco runs itself on the master worksheet instead.
I want to use script to programmatically create Hyperlink in multiple cells. The Hyperlink will trigger a macro event and run a simple command using the "PC Name" as input variable. The Hyperlink should have:
-Text to display: - different "PC Name" in each cell
-Type the cell reference: - corresponding to the cell where the Hyperlink is inserted
-Link to: - Place in This Document
I tried using cell formula, but it always default to "Existing File or Web Page".
ExcelHyperlink.png
Trying to select multiple shapes based on shape properties. The missing piece is the code to select the shapes. The examples in the help use the 'Array' function with a list of shape names.
Is there a way to select a random set of shapes without using the Array function?
Or how does one build a list for use with the Array function?
I'm trying to programmatically add and remove the Outlook 11.0 Reference Library in Excel 2003. So far, I have found the following code which successfuly adds the reference:
View 3 Replies View RelatedTo refresh a UserForm ListBox when new items are added, I Unload and Show the Userform as part of the procedure.
Code:
Private Sub CommandButton4_Click()
'do stuff
'do some more stuff
'do a few more things
'finish doing stuff
Unload Me
UserForm2.Show
This returns the UserForm to its Initialized state and displays the refreshed list.
But, this requires the user to click CommandButton6 to continue adding additional items. If CommandButton4 code could click CommandButton6, the UserForm would Show in its "add item" state...I think. Is there a way to programmatically click a CommandButton?
I've already tried 8,321 ways without success...(OK, maybe only 4 ways)
I have a workbook that contains a form with cells that use choice list validation. It also contains a worksheet with all the choice lists. Each choice list is a named range. The form is used to collect data that is new and from historical records. The historical records contain entries that are no longer allowed in new data. To allow for the older data I have choice lists (named ranges) that contain both the new and obsolete choices. This is a data problem waiting to happen. I would like to be able to have the user tell the workbook if the data is new or historical. If new I would only provide them with current choices. If historical, I would provide them with all choices, current and obsolete.
I would link the VBA script to change expand the ranges when told the data being entered is historical. The problem is I don't know how to code such a script. For example one of the cells has as current choices W, M, D. The range would be named MoistureState and the range would be A2:A4. There is one obsolete choice, E. If the Historical data button was clicked the MoistureState range would change from A2:A4 to A2:A5.
How would one go about expanding the named range?
What I have is a list of data on a sheet. Through some code, I would like to visualize my data in a certain way. What I currently have is a grid, each square corresponding to a single data point. I would like to assign a tooltip to a label that will be sitting on top each of these squares.
What I have so far: I have a userform which will be populated by labels programmatically. I am doing it like this because there are at least a few thousand labels to be created. I am currently doing it like this:
Code:
counter = 15
For i = 1 To 20
For j = 1 To numrows
[Code]....
Essentially, what I have is a grid, and I would like to change the letter "x" in the label to another color when the mouse is hovering over the label.
Is this the correct way of achieving what I want, or is there a better way? Is there a way of assigning a single mouseover/hover event to the label once it has been created? (All these labels can be handled by a single function to just change the text color).
I am trying to create a table (listobject) from a textfile datasource on a shared drive.
How to get this over the line, I have the following code which I am struggling to finish.
Code:
Sub Testing
Dim wb As Workbook
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim lo As ListObject
Const Pathway As String = "F:Scripting"
Const File As String = "Log.txt"
[code]....
I have a suite of subroutines and functions stored in an .xla file, which other users have access to. It's installed on the local machine via macro which installs a user defined menu to access them from. Howerver, when an XL chart worksheet is active, this menu doesn't appear and there are a couple of functions where it would be useful to have access to when viewing such a chart. I've noticed user defined toolbar icons are available in this view, so would like to use these to access the relevant routines. I know you can insert them in code (macro recorder) but is it possible to assign macros to them from the same code? Alternatively, is it possible to show my user defined menu in chart sheets as well as normal worksheets?
View 9 Replies View RelatedSuppose I have a userform named myUserForm and a checkbox on it named myCheckBox. How on earth do I remove myCheckBox from myUserForm at run time? I've tried every which way I can think and I always get "error 444". Some things I've tried are:
myUserForm.Controls.Remove "myCheckBox"
myUserForm.Controls.Remove(myCheckBox)
myUserForm.Controls("myCheckBox").Remove
myUserForm.Controls.RemoveControls "myCheckBox"
I've put this in a standard module, the ThisWorkbook module, even in the myUserForm code module and each time it's the same error.
I have a very complex report with a variable numbers of rows in multiple sections. I am evaluating whether to display each row with a formula in the first column (those that I don't want to display have a "H" in the first column).
The following code works fine, but it takes forever. Ideally I wanted to trigger the code on the Worksheet_Activate() event but its so inefficient that I can't do that.
VB:
On Error Goto Errorhandling
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.Calculation = xlManual
Application.StatusBar = "Processing..."
[Code] .....
I have wirtten a sub routine that builds a userForm dynamically based on user specific data. The form builds just fine and 9 times out of 10 the code at the end of the sub routine removes the form from the workbook. Since it's built on the fly I don't bother renaming the form. The times when it does not remove itself and the user restarts the program, part of the code written to the forms module references "UserForm1". and the the code halts. I know there is a way I can verify that UserForm1 has been removed and if not remove it before executing the next sub routine, I'm just tired and can't remember anything. Note that the UserForm1 contains CommandButtons that direct the user to Static UserForms that do not get removed, and those can trigger the sub routine to rebuild the dynamic UserForm.
View 3 Replies View RelatedI have both Excel 2003 and Excel 2007 installed on my computer, using VBA code is it possible to specify which version I open an excel document in?
View 2 Replies View Related