I have a right click event for a cell where I create a custom commandbar with single or multiple control buttons based on the cell value. I want to link each control button to another cell in the same sheet.
View 2 RepliesI've created a CommandBar Menu using Macros. It's not a linear code, I've used Do-While to create the different objects and embedded objects. As you can guess all objects refer to the same Macro. And in this Macro is were I've got the problem. I can't come with a code to get the name of the object or the index reference, and thus I can't create the actions for the different objects in the menu.
View 3 Replies View Relatedwhen open xl app my addin is installing, but when excution gets to delete the my addin an error appears tells :invalid procedure call or argument.
Sub workbook_open
On Error Resume Next 'Just in case
'*Delete existing Super Menu if it was left.
Application. CommandBars("Worksheet Menu Bar").Controls("Accountant Menu").Delete
On Error Goto 0
End Sub
'The following code creates a new commandbar name Visual Basic Editor, which on event delete the VBE module1. How can I tie the built in visual basic editor commandbar to this.
Sub bar()
Set mybar = CommandBars("Tools").Controls("Macro")
Set mycontrol = mybar.Controls _
.Add(Type:=msoControlButton)
With mycontrol
.Caption = "Visual Basic Editor"
.FaceId = 1
.OnAction = "MySub"
End With
mycontrol.Visible = True
End Sub
Function mysub()
With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
.DeleteLines 1, .CountOfLines
End With
End Function
I have an excel that has dropdown column from form control, in F4 there is a formula of vlookup that take the range from A2 to B20 in consideration but the reference cell that is given to vlookup formula to pick corresponding value is E4 which is also the reference cell of dropdown box and therefore its not showing any name rather than a value, problem with the formula is that when we select any name from dropdown it does not respond to any value in F4 despite changing values in E4.
I am using these setting for the first time.
Image & excel file is attached
drop down.png
I am looking to connect 2 form control boxes and have the second box run 2 different types of macros. The first box will have only 2 options - select by week and select by month.
The second box should show the list of weeks or months based on the selection in the first box. Then for the second box, if weeks is shown, a week macro should be run whenever a week date is selected. Similarly for the list of months, a month macro should be run whenever a month is selected.
I have been trying to do this for more than a week (after posting on this board) without success. I apologize if this seems like a duplicate post.
After countless hours of finding a way to click a link between divs. My Current code:
Code:
Sub test()
Dim ShellApp As Object
Dim ShellWindows As Object
Dim IEObject As Object
Dim strURL As String
Dim Document As Object
[Code] .......
However, When trying to go trough the link objects, it just skips the whole code. I Tried numerous ways of trying to click a link:
HTML Code:
<ul class="th-menu2" id="C6_W28_V29_mainmenu" style="width:171px">
<li class="th-menu2-item"><a id="C6_W28_V29_IC_INBOX" class="th-lk" href="javascript:void(0)" *******="htmlbSubmitLib('htmlb',this,'thtmlb:link:click:0','myFormId','C6_W28_V29_IC_INBOX','IC_INBOX',0);return false" title="Inbox" onfocus="thtmlbSaveKeyboardFocus('C6_W28_V29_IC_INBOX');" oncontextmenu="return false;">Inbox</a></li>
</ul>
Is there some reason why the VBA code is skipping the for each function?
I have a form that collects four pieces of information from the user and places each into columns A, B, C, D on a sheet. I would like to automatically add an option button to column E so that a user could select it and then click a control button to delete the corresponding row.
Is this possible or is there maybe a better way to do this in Excel 2007.
I have two worksheets, one with detail monthly information and one with YTD information. So let's say the three numbers I want to capture in the YTD sheet are in columns B, G and I on the monthly sheet. January's data might be in B5, G5 and I5. February's data is in B12, G12 and I12 and so on.
On the YTD sheet in cell C2 I link to Monthly!B5 and in C3 I link to Monthly!B12 so cells C2 to C13 on the YTD sheet show the monthly totals from column B on the detail sheet. On the YTD sheet, cells C20 to C31 show the monthly totals from column G on the monthly sheet, so cells G5, G12, etc. And finally, cells C40 to C51 on the YTD sheet show monthly totals form column I on the monthly sheet.
In the past I've always created all these links manually. After creating the links in C2 to C13 on the YTD sheet, is there a way to use a formula in C21 that uses the link in C2 to create a link for G5?
Within the ComboBox properties, is there anyway to control after "enter" his hit, you move to the right instead of down (similar to the edit under Tools/Options)?
View 9 Replies View RelatedI have this code below which creates a CommandBar but my problem is that I thought the line of Code .Width 100 would make the width of the actual CommandBar increase if I adjusted that number, but this doesnt seem to be the case. The title in only shows Nursing Sta instead of Nursing Staff Rota. How can I make the CommandBar wider than is?
View 4 Replies View RelatedI am trying to disable a shortcut menu for a commandbar I created. I am able to disable all shortcut menus for all commandbars with this:
Application.ShortcutMenus(1).Enabled = False
But, I'm not trying to disable rightclicking for all commandbars, just one. Is that possible? As far as I can tell, the "shorcutmenus" property is a direct child of application.
How do you use the Application.CommandBars in the CommandBar Object to find a button.
View 1 Replies View RelatedI have programmed an Excel file that removes all the standard commandbars from Excel and then opens a custom commandbar, I built, with limited functionality ( Named "Limited"). When I open this file on another computer, since that custom commandbar does not exist in that copy of Excel no Limited commandbar shows. So I created a macro that builds the custom commandbar when a file is opened. What I want is when the file is opened on subsequent times that it checks to see if that commandbar already exists. If it does, it skips the macro which builds this custom commandbar for the first time. It then disables the standard Excel commandbars (code written already) and enables the "Limited" commandbar (code aleardy written).
View 2 Replies View RelatedHow can I save a new xlt or xls so that the toolbar settings I am viewing will always reload with excel. I have tried purging all items from my xlstart locations, but unwanted (and unchecked) toolbars keep reappearing when I restart excel.
View 4 Replies View RelatedI have create a Command Bar, my problem I'm trying to understand is this
1.Can you amend the font colour of the any Captions on the CommandBar?
2.How can I get the Caption to pick up from a range and use the range name as the Caption?
I have named a range in a worksheet that I want to pick up and use as the range. Below is my CommandBar code
I had made a Workbook with my own command Bars, and everthing is ok .. the problem is when i copy it to anthor pc. i have got an error msg. and when i check the code (debug) i've found the problem is with that command bars ( it's missing )
and now the Q is:
how to copy a workbook with it's all add-in's and customised bars ?
Is there any way in VBA to refer to a control in its own event procedure without referring to it by name/hard-coding?
It might be clearer to explain by a dummy code example:
[Code] ......
I'm seeking what I would need to replace Line1 with.
I have created a userform within VBA which has a TreeView Control and a Spreadsheet control on it.
I have populated the TreeView control with data and what I want to be able to do is to drag the nodes off the TreeView control to the spreadsheet control.
I can drag onto a normal worksheet but not onto the spreadsheet control (the no drop mouse pointer keeps showing).
How do I determine which control the user is currently modifying on a multipage form (either changing, enterying or exiting the specific control). when I use "userform1.activecontrol" i get "multipage1" as the control name but I need the actual control on the specific active multipage. (also the .TABINDEX is for the multipage regardless of the on-page control) I use a generic data-field change SUBroutine so need the control name (and the TABINDEX) to provide my SELECT CASE. (so every fieldname_CHANGE calls the same SUB [with no parameters])
View 3 Replies View RelatedI have a column who's content is determined via about 6 nested if statements from data on that row. That cell is then conditionally formatted to a certain color based on the text that is ultimately printed from the nested ifs (simply an extra visual legend for the text). All of this contributing info and about 1000 lines items make a very large and difficult to print page. What I am trying to do is a make a summary sheet that simply takes the index number of these 1000 rows and copies or links the conditional formatting of the mentioned cell onto this number on another sheet. I have already linked this status cell and put it adjacent to the index number which works well in that with two columns I can show the index and status but if I could combine the color of the status cell onto the index cell, it would be even better. I am pretty sure another conditional format for this summary sheet would not be possible or be extremely complex since the contents of the index cell I am conditionally formatting have no bearing on the conditions for the format. Was hoping there is some VBA magic that could simply mimic the conditional formatting from one cell and put it on another.
View 1 Replies View RelatedI have a macro that inputs a sheet and cell from the user. when the macro tries to make a formula it outputs =data!'AE3'. I need it to output =data!AE3
Dim Sheet As String
Sheet = InputBox("Take Data from where?")
Dim startcell As String
startcell = InputBox("Start Cell?")
ActiveCell.FormulaR1C1 = "=" & Sheet & "!" & startcell & ""
I would like to have a link in a merged set of cells that puts the insertion point to another cell. Scenario: In cell k30, the user puts in the amount of credit card debt a individual has, and scripts the user based on that information. Later on, they get a more accurate running total of the amount of debt and need to change the amount in k30 for an accurate quote. After ther running total, in cell j172(a merged box for instructions) i instruct the user to be sure to enter the new total in cell K30.
Questions:
1. Is there a way to put a button or a link that would put the insertion point back up to K30 automatically, but allow the user to change it themselves and not change it automatically?
I would like to ask if there's a way that i can create a link in a cell to another cell. Sample: in cell A1: there's a word "click to go to cell A150" in this case i dont have to use scroll down to go to cell A150.
View 2 Replies View RelatedI have a HUGE dataset (more than 1 million rows). I'm trying to find the optimum cell range size - 10 rows, 100 rows, 682 rows, whatever - for a SUM formula. (The formula would be in every row, looking down at the next n rows).
Is it possible to control the SUM cell range specified in a formula in cell (X) with a value in cell (Y)? In other words, the SUM formula in cell (X) would somehow refer to cell (Y) to determine the SUM cell range.
Ideally, I would like to then use Solver to find the optimal value for cell (Y).
Is this possible with just formulas? Or is VBA required?
I have a column on a spreadsheet containing the last four (XXXX) or in some cases six (XX-XXXX) if there are duplicates of the last four SSN. I manually enter these as needed. I also create a folder that will be named the last four or six in the same format that is in the excel spreadsheet. Is there a simple way to automate the linking of these cells to the external folders?
View 3 Replies View RelatedI have a macro that copies a sheet and renames it:
Copies "Q114" and renames it to "Q214".
Q114 is defined as OLD in my VBA code
Q214 is defined as NEW in my VBA code.
I also have a third sheet, call it "results", which picks up cell values, ie cell A1 in "Q114", but now I want it to use cell A1 "Q214". The values hasn't been filled in, so I need a link between them:
[Code] ......
if it is possible to link the name of a tab to a cell in its corresponding worksheet.
View 9 Replies View RelatedI have is a summary worksheet with two columns. One column is for Project ID number and the other is an address link to a specific cell in the worksheet corresponding to the Project ID. Perhaps the best way to explain this is by giving an example:
Project ID (Column 1)
00-111
Balance (Column 2)
='C:Financial Sheets[00-111.xlsx]Phase_21'!$H$1
As you can see the spreadsheets are named after the Project ID number. What I would like to do, if possible, is instead of typing in the name of the spreadsheet for each project in column 2 (Balance), I could somehow reference the cell value in column 1 (Project ID). Something like this:
Project ID (Column 1)
00-111
Balance (Column 2)
='C:Financial Sheets["A2".xlsx]Phase_21'!$H$1..........
Can you link a cell to a sheet tab. If yes how?
View 4 Replies View Related