i need some code to be able to toggle design mode, i am creating embedded flash games in excel and i want to be able to change the game using a drop down and this code:
View 3 RepliesCan you change the height of a TextBox in a workbook when not in Design Mode? Like just grab the bottom and resize.
View 2 Replies View RelatedFor some strange reason the workbook I have been using on a daily basis has suddenly decided to go into design mode and stay there. No matter how many times I click the design mode button in and out it always stays in and I can't use my buttons.....it just selects them?
View 9 Replies View RelatedI have a production report card spreadsheet with numerous checkboxes. Originally I also had text boxes for user input - which I have since removed all of and simply unlocked cells for user input (I protect the spreadsheet). My problem is that now that I have removed ALL of the textboxes - I get the error message "Can't exit design mode because Control 'TextBox3' can not be created." everytime I go to lock the spreadsheet after tweeking or upon opening of the file. I also cannot use the checkboxes now because I am stuck in design mode.
Any thoughts on what might be causing this...or better yet...how to stop the error msg altogether? I googled and no answers...some theories that it was a bug with excel2003...but I find it hard to believe as I found posts for the same message of users of WORD2003...so I am thinking it might be VBA related?
I've created a spreadsheet that has a number of command buttons acting as hyperlinks navigating to other worksheets within the workbook and other files. The buttons only function when in design mode and no matter what I try the document will not open in design mode. For ease of use I wanted to avoid training people on how to go into design mode, anyone aware of a work around?
View 3 Replies View RelatedI have been writing VBA code in Excel for about 4 years, but I am self-taught, so I only know what I have had to learn. Recently, I learned that I could run a macro from a cell formula. I created several hundred cells (on multiple worksheets) that include calls to various macros. I only want to run these when I know they need to be updated, not every time I change a cell value.
I turn on Design Mode and try to modify my worksheet (adding or removing columns, rearranging stuff) and some macro(s), somewhere(?) execute every time. This can sometimes take 15-20 seconds for everything to update.
Did I hit some magic key sequence, somewhere? How is it that some of my macros, somewhere are running with Design Mode enabled (and turning it OFF, by the way)?
I am a happy user of Office 2007 and a casual coder. I am familiar with most of the basics of coding and enabling macos. However today, when I attempted to add some code to a workbook, I noticed that Excel would not actually enter design mode. Even though the design mode button will toggle and stay in the "on" mode, I cannot access VB property panel for any controls, even standard contols. Also, I am forced to CTRL-CLICK objects to select them and then am only presented with standard "Format Control" option, not object properties.
One other odd sympton... I can use the macro recorder to add code to a control (a checkbox for example), but when I try to manually add a line of code referencing that object (using the default name), the VB editor does not recognize the object. For example the IDE would not recognize and code a line for the "checked" property of a checkbox control when I check the box while in macro record mode.
These symptoms suggest to me that there is something wrong with the design mode "switch".
This all used to work fine, but it's been a while and many MS updates to my Windows 7/64 machine (now about 4 years old).
Is it possible to use design mode to create a button for multiples macros?
What i want to do is create like a list, and every selection run a different macro. I have a report that i want to use macro to make different filter selections, it would be about 20 different filters combination, so 20 buttons would be a mess... Is that possible or can i do it in another way?
And is possible to link the filter value to a different cell?
I am working on a workbook that contains drop-downs in various cells. The source for these drop downs are several Named Ranges on a tab named 'Drop Downs'.
I am locking the whole spreadsheet down so that people are not messing with the format, etc. (which is the current problem). So I don't allow people to add/delete rows, etc.
I am trying to think of a good interface to allow users to modify the entries in each drop down list (i.e. they may need to add a person to the "Resources" Range which feeds into a drop-down box elsewhere).
So, people should be able to add/delete items. I was thinking I would put a little 'X' next to each entry, such that when pressed - it would delete the corresponding entry. (Using the Assign Macro on a Text Box). However, I have to assign each 'X' a method to call - and I can't pass in an argument - so if I assign them all the same macro name - I won't be able to figure out which one was pushed. And the number of 'X' will be dynamic, so I can't hard code a bunch of functions. I don't really have a good idea for allowing additions yet (other than a button that prompts for the name and then adds it in).
I guess I could just use a Form that allows editing of the data (that would be easy and powerful to do), but I don't really want to do that - I want it to be editable within the excel spreadsheet itself.
Is there a way to keep the drop-box on a combobox from showing at all? I am trying to build one that has several different values that will just cycle through when clicked.
Here a bit of code I am using so far, just as an example:
Private Sub ComboBox1_DropButt*******()
If Range("F7").Value = "Yes" Then
Me.ComboBox1.Value = "No"
Else
Me.ComboBox1.Value = "Yes"
End If
End Sub
I am busy with a project using VBA to populate a spreadsheet based upon the data entered into a particular cell. The problem that I am experiencing is that I populate the initial cell via a combo-box which I cannot exit unless I click elsewhere with my mouse. The code that I am using to populate the other cells is as follows
Private Sub Worksheet_Change(ByVal target As Range)
Application. ScreenUpdating = False
On Error Goto dump
Dim r As Range, MyRowNum As Long, ws1 As Worksheet, ws2 As Worksheet, strPriCode As Variant, _
strNAPPIE As Long, strSInCost As Currency, strSInDesc As String
Set ws1 = Worksheets("SINVOICE")
Set ws2 = Worksheets("STOCKIMP")
Set r = ws1.Range("B11:B35")
If Intersect(target, r) Is Nothing Then Exit Sub
If target > 0 Then....................
Is there a way to change the text on a toggle button in the code below? Basically I would like for the toggle button to begin with the word "START" on it and then when clicked changed to the word "STOP". Of course then when the user clicks on "STOP" it changes back to "START".
View 3 Replies View RelatedI would like to set the value of a toggle button when the userform is opened so that it coincides with the what columns are hidden on screen. Below is simple example which I have not tested or debugged. It was just what i was thinking.
VBA code
Sub Macro2()
'if col A is hidden
If Columns("A").EntireColumn.Hidden = True Then
'make hidecalcs toggle button true (sunken)
Userform1.HideCalcs.Value = True
[Code]....
This code toggles between a blank cell and an "a" each time the user selects V81.
Code:
If Target.Address = "$V$81" Then
If Target.Value = "" Then
ActiveCell.Value = "a"
Else
ActiveCell.Value = ""
End If
End If
I'd like to expand it so this toggle works not only on V81, but also on V89, V100:105, and V120:124.
I currently upgrading some add-in to Excel 2007. This one concern a toolbar or Ribbon as they call it now. I am creating a toggle button using XML
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab id="rxTrading" label=" Trading" >
<group id="rxDownloadStockData" label="Download Stock Data" >
<button id="rxDownloadData" label="Download" onAction="rxDataStock_Download" />
<toggleButton id="rxEnableDisableDowwnload" label="Enable Download" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>
How can I change the name of toggle button to "Disbale Download" after clicking on the toggle button? I am trying to find the answer on WROX Excel 200 VBA but I am lost.
I have written a macro to loop through files and subfolders to open a specific file for a specific month. I extract the datasets for every excel file into a master template. The code works flawlessly when all of the files have the .xlsm extension. However, I need to open .xls file formats also. When my code opens the first .xls file, named "Staffing Plan.xls" it opens it and automatically makes it "Staffing Plan1 [Compatibility Mode]. I can't open the file in the original "Staffing Plan.xls" file format to read data from. How can I fix this?
View 3 Replies View RelatedI put together about 10 separate macros that will log you into a site using the values in a given cell. I was having trouble with the last one because of AutoComplete remembering the username. So I put in an IF statement, and when I used F8 to go line for line, it works perfectly. However, when I click F5 and just let it play through, it doesn't log in. I tried adding a 5 second delay, but that didn't seem to work. I still get a run-time error when I hit play.
It get hung on this line:
Code:
If doc.getElementById("user_name").Value = cUsername Then
But the complete code is this:
Code:
Sub StreetLinks_Login()
'On Error Resume Next
Dim cURL As String
cURL = Worksheets("Sheet1").Range("B9").Value
[Code] ..........
I have found that this only works on the sheet being viewed, how can I make it work on a different sheet than the one that I am on?
Option Explicit
Private Sub Worksheet_Calculate()
If Range("C1").Value = "5" Then
ActiveSheet.Shapes("rectangle 1").Visible = True
Else
ActiveSheet.Shapes("rectangle 1").Visible = False
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
I have a drop down sub pasted to worksheet:
Private Sub ComboBox1_Change()
ComboBox1.List = Array(100, 200, 300, 400)
If Range("I11").Value < Range("N11").Value Then
If Sheets("Profile").Range("K18").Value < ComboBox1.Value Then
Range("I11").Interior.ColorIndex = 2
Else
Range("I11").Interior.ColorIndex = 3
End If
End If
End Sub
I want it to change the cell color on drop down change. How can I modify things to have the change in drop down selection?
I have a togglebutton for an excel sheet in use for scoring psychological tests. When pressed, the button higlights the cells where a raw score is inserted.
What I need is a code to higlight the togglebutton when pressed, eg. green (colorindex 4). The button is white. How to do this?
Below is the code, It has more lines than needed, but it works.
Private Sub ToggleButton1_Click()
'FKV TOGGLE'
ActiveSheet.Unprotect "manisk"
If ToggleButton1 Then
[Code] ........
Is there any way you can use VBA to assign a key to toggle between two different cell formats? I want to toggle a cell from white border with black text to black border with white text and visa versa, with the same key or button. I've searched the posts but can't find this mentioned.
View 6 Replies View RelatedI have a coworker trying to create a dropdown list and is using wingdings for the checkmark, x, and other symbols for filling out for. Everything works great except when you click in the field the drop down box doesnt show the wingdings font but the corresponding arial font. Is there a way to change the dropdown box to wingdings because currently the dropdown box shows various 'u's with different grammatical stresses.
View 2 Replies View RelatedIn Excel 2007; I need my drop down list to then change the next cell down to a series of different values? How do i do this? i have created the drop down list, but can't link the values to the item selected in the drop down list. The JPG attached shows a basic set up of what i am trying to do: The drop down list contains various product models (yellow) Once selected this then changes the m2 value (red) which is linked to the formulas in orange; product factor is entered manually.
View 3 Replies View RelatedI have 18 drop down lists that are identical and named DropDown1, DropDown2, DropDown3, etc. Each one references cells (cell link) E1, E2, E3, etc respectively in another worksheet Lookup that is hidden. I am trying to write up some code that when the user selects any drop down, depending on which one it is, it passes certain values to another Sub which has cases. I got my first ideas from an old thread Drop Down List Box but there were no variables being passed.
My code originally worked with one variable being passed (x), but now I am trying to pass 2 (x and nrow) and I get an error in the Sub DropDown1_Change(): ByRef argument type mismatch. Below is my coding, I only included a few drop downs for this example. The Cases are based on the cell link (ie Case 2 is when the cell link shows '2' in the Lookup sheet) I only used a message box for testing purposes. I will have to do some other stuff but I need these variables passed first before I can continue
Public x, nrow As Integer
Sub DropDown1_Change()
x = 1
nrow = 2
Call get_surcharge(x, nrow) 'this is where I get the error
End Sub
Sub DropDown2_Change()
x = 2
nrow = 3
Call get_surcharge(x, nrow)
End Sub............
I inherited a spreadsheet with a combox box, drop down list for user to select a project by name. The cell right below uses a lookup function to select the code listed with that project name from a list on a separate worksheet. When I edit the codes on the list used as the source for the lookup, the new code will appear as it should.
I want to edit the drop down list that appears in the combobox. I cannot find the source for the drop down list. Any ideas where/how to look for it?
How do I change the color of a row, based on the users selection from a drop down box in that row? I have four driving types in my drop down , Towing - Town Driving - All highway - Mixed. I would like each driving type, if selected, to change the color of the entire Row of Data to a different color. I have been able to use conditional formating to change the drop down cell itself, but that is it.
View 1 Replies View RelatedI have two drop down menus: Class Name and Class Period.
Class name returns reading, math, science, or social studies.
Class period returns 1,2,3, 4,5
I need a formula for a cell that will return the name of the first student in whatever criteria is selected by the two drop down menus.
Example: If reading 2 were selected, I would want the name of the first student in reading period 2.
I did start out with nested If's combined with ands, but that is too many nested ifs. I figured there was a better or easier way to do this.
Formula I thought of: =IF(AND(G3="Reading", H3=1), '[Reading 8.xls]Sheet1'!$A6, IF(AND(G3="Reading", H3=2), '[Reading 8.xls]Sheet1'!$D6)) etc.....
But if I use that there would have to be 20 nested ifs, which I think I read someone nested ifs cap at 9.
I have 13 sheets in my workbook (one for each month plus a GlobalSettings). In each month sheet I want to create a change event that prompts a UserForm when they select "Yes" from a drop-down validation list if it happens to be a month prior to the current month.
This is the code for the change event:
I have a chart where half the users want a black backgroud and half want a gray background. Is there a way I can change the chart colors based on a textbox value? I would have the user choose a value (say, "Dark Background or Light Background") and then, upon commencing the macro it changes the chart.
View 4 Replies View RelatedIs it possible to change those AutoFilter drop-down arrows that are active? As it is now they are hard to spot which is in use.
View 5 Replies View Related