Editing Existing Macro To Preserve Option Button Settings Upon Activating Worksheet With OBs
Sep 19, 2013
I have been using this code and just noticed that it resets all of my options buttons to false when I exit and then re-enter (activate) the sheet with the option buttons.
I see where this is going on, but don't know how to correct it. I only want the option buttons changed to false if
The Sol named range is something other than "Primary Vendor". It seems to call the macro ClearOB whenener the sheet is activated.
Private Sub Worksheet_Activate()
If Range("Sol").Value = "Primary Vendor" Then
For Each OB In ActiveSheet.OptionButtons
OB.Enabled = True
Next OB
ClearOB
ActiveSheet.ScrollArea = "A1:K58"
[Code] .......
View 1 Replies
ADVERTISEMENT
Sep 26, 2006
What command should I use if I want to activate sheet1 by clicking a coomandbutton1 on sheet2?
View 5 Replies
View Related
Aug 6, 2009
i have a list of about 20,000 phone numbers that I need to edit.
all start
01 123456
01 123457
01 123458 etc
I am trying to create a macro that will change them to
+001123456
+001123457
+001123458 Etc
However, no matter what I try it always comes out as
+001123456
+001123456
+001123456
F2 Copy and paste is going to take me forever, and I am sure that there must be a quicker way to do this.
this is my first time creating macros so am completly lost.
I want to keep the text that is in the cell that I am editing, just add the country code and delete the space.
View 9 Replies
View Related
Feb 2, 2007
I have two option buttons (Designerbutton1 & Designerbutton2), when I copy them and paste them to a different worksheet their names change to Optionbutton# (# = next available number). Anyone know why & if I can prevent this. I cannot have a code that says Optionbutton16 should be renamed to what I want it to, because right now 16 is the next available # but what if I add an Optionbutton16 later on my own.
View 2 Replies
View Related
Oct 23, 2007
I am having trouble writing a macro that checks the value of an option button to change a string accordingly. The ActiveMonth variable is used to copy information across from one work sheet to another, with the worksheet depending on which of the option buttons is selected.
Static ActiveMonth As String
View 9 Replies
View Related
Dec 26, 2007
I have done much searching, found some relevant, similar postings, but I have not been able to make it work for my code. I want to have multiple option buttons and have a macro determine which one is selected. One similar posting had macros run from the clicking on the option button, but I want the macro to determine which is clicked and then return some functions. From my research...this is my code.
Sub temp()
If OptionButton1.Value = True Then
Range("C1").Select
Selection = "OB1"
Else 'assuming two buttons and first is false
Range("C1").Select
Selection = "OB2"
End If
End Sub
This gives me an "Object Required" error on the first line of the code.
View 2 Replies
View Related
Sep 10, 2013
I know Excel is supposed to guess if there is a heading row or not when you're manually sorting a group of columns. But, more often than not it seems, it guesses incorrectly, forcing me to go through the multiple steps of the "Custom Sort" menu. Most of the manual sorting I do does have a Header row.
Is there a default setting somewhere to force the sort to always assume there is a Header row? I can't find any.
View 2 Replies
View Related
Feb 19, 2009
A VB macro is created on a excel sheet. & assigne that macro to one button (by using Customise option) which is added on Menu bar.
So what i want to know is that is it possible to make the button works in all excel sheet.
Because when ever a new sheet is opened the button which i have added is visible. But when i click on that button the macro will not work or it opedns the original sheet on which macro was designed.
View 9 Replies
View Related
Dec 23, 2009
I'm verrry new to editing VBA code and I've been trying to figure out how to send a column of data ("Min Data" AB3:AB21 from "Sheet1") to a worksheet ("F4" - Min) and sending another column of data ("Max Data" AC3:AC21 from "Sheet1") to a different worksheet ("F4" - Max) by using only the one button ("Record Data" from "Sheet1"
I've been trying to figure out ways to edit the VBA in "Module 1" to make this work
Can anyone help me out here? If what i'm asking for isn't clear enough, just let me know and i'll try to explain in more detail.
View 6 Replies
View Related
Feb 23, 2014
I am brand new and I don't know a ton about macros and programming in Excel.
What I'm trying to do here in this attached spreadsheet is at the end of inputting my line, I want to hit the tab (marked in red to make it clear where) button in the cell and have it automatically create another blank line but keep the formulas the same.
Basically I just want to avoid having to right click and insert line and then drag the formulas column by column down which is difficult.
See attached : TEST2_2014 Sales & Commission Spreadsheet.xlsx
View 5 Replies
View Related
May 19, 2013
When my worksheet is activated, code is executed which defaults the values in my userform controls. The users enter the appropriate data, and with the clicking of a command button, the data is entered into the worksheet. When that is complete, the idea is to return the user to same form, in a default value so they can enter in new data for submission. This will continue until the user selects the Exit button onthe userform.
Code:
...
With wshairport
.Range("A" & llastrow).Value = uf2.ident
.Range("B" & llastrow).Value = uf2.freq
.Range("C" & llastrow).Value = slocation
.Range("D" & llastrow).Value = slat
.Range("E" & llastrow).Value = slong
End With
Application.EnableEvents = False
uf2.Activate
This is resulting in an 'Object doesn't support this property or method.'
I'm not sure about the appropriateness here of the 'Application.Enableevents=False" here. Since the fields of the userform have change events associated with them, I need to disable that what the userform activation code defaults them.
View 2 Replies
View Related
May 11, 2012
I have this code that ideally should change font color in usedRange to red upon button click. The problem is it's not working and I've been pulling my hair out all day (not that there's much left of it)
HTML Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim UsedRange As Range
Dim cel As Range
[Code]....
and the related "End If" this works i.e. if I click on any cell within respective sheet font in used range all changes to Red but I only want this to change on CommandButton1 click which I have inserted in sheet.
View 7 Replies
View Related
Apr 29, 2013
I care about is the line starting wks4.Cells(Di, 2) = I am trying to sum data from a variable length column in worksheet 3 and place the result in column 4 However, when I hit enter after entering the formula into =SUM() I get
Compile Error:
Expected: list separator or )
[code]
Sub Macro1()
Dim wks3 As Worksheet Dim wks4 As Worksheet
Set wks3 = Worksheets("Sheet3")
[Code].....
View 4 Replies
View Related
Dec 6, 2004
I'm designing a custom toolbar in VBA and have managed to create a toolbar with buttons.
However, I've been battling away trying to edit the buttons images so that I can design the button image to match the nature of macro assigned to it. The problem is that if you record yourself doing it, the macro written in VBA doesn't seem to recognise the editing button actions.
Does anyone out there know of a way to custom edit a button image in VBA?
View 9 Replies
View Related
Feb 2, 2013
There was an option in excel 2007 in the pivot wizard to use an existing pivot table report as the source for a new pivot. I cannot find this in Excel 2010.
View 2 Replies
View Related
Sep 5, 2013
- Construct a macro to import a csv-file to my worksheet named "Info". The data in the .csv-file should start in row A5 in my worksheet.
- If the worksheet "Info" already has data, I want to overwrite the existing data with the new data starting in row A5
The directory of the csv-file is C:Testmycsvfile.csv
View 3 Replies
View Related
Apr 25, 2007
I'm adding a command button at run time using excels help code;
Worksheets(newWorksheetName). OLEObjects.Add ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False, Left:=325, Top:=24, Width:=50, Height:=20
I want to change the buttons properties including its caption etc.. I've tried using'
Worksheet(newWorksheetName).OLEObjects("CommandButton1").Caption = "Do"
But i get a run time error. Also, the add method doesn't specify what the button is called, any clues?
View 2 Replies
View Related
Mar 23, 2007
All code is called from another workbook
This fails on the second line with a type 9 error, ie worksheet not found
Workbooks("RENT_EXPLANATION.xls").Activate
Worksheets("Rent change details").Activate
This works:
Workbooks("RENT_EXPLANATION.xls").Activate
Workbooks("RENT_EXPLANATION.xls").Worksheets("Rent change details").Activate
Surely both should work since the default qualifier for the worksheets object is the activeworkbook? This only fails in Excel 2003, in the same app. in Excel 2000 it works.
View 4 Replies
View Related
Dec 5, 2009
I have a User Form which i want to use in such a way that editing the worksheet directly is not disabled while the userform is running. I would also like to use the right button mouse menu(cut, cpy,paste) while i am using the user form. I am using two Monitors with my PC and i want to work with User Form on one monitor being able to edit worksheet without having to close my User Form.
View 2 Replies
View Related
Feb 23, 2008
I have a macro that copies selected data from various cells in WS1 to WS2's next open row, using offset to step to each new open cell in that row.
View 14 Replies
View Related
Jan 22, 2008
I'm trying to add a message box that will appear when you activate a worksheet in a workbook. This is what I have, but it's not working.
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh. Name = "P2 Forecast" Then
MsgBox "Ensure you have locked your forecast on the Sales Forecast Tab prior to working your P2s"
Else
End If
End Sub
View 9 Replies
View Related
Mar 5, 2013
I have a worksheet which is used to display analysis data to the user. The sheet will be further protected with only some cells available to the user. I've used the attached code to retrieve some data from the sheet, clear a bunch of cells to reset the sheet and reset a graph (the graph itself is plotted as a log-log with the 1 to 100 on the Y-axis, hence the resetting of data to 0.5 to push it below the axis and make it invisible ready for later data dumping).
The code worked fine until I protected the worksheet. I have set the charts to unlocked using the format option in the chart area prior to locking. When I run the code though, it stops on the line indicated with the error "Application-defined or object-defined error". I've double checked and Chart 7 is the correct chart, and it is unlocked according to its format properties.
VB:
'get date of survey and equipment number for retrieving the data from the archive
dateSurvey = Worksheets("Calculation Page").Range("B2").Value
equipNum = Worksheets("Calculation Page").Range("F2").Value
'stop screen updating
Application.ScreenUpdating = False
[Code]....
EDIT: Oops, I've just noticed that even if I unprotect the sheet, I get an error on the .select within the seriescollection stating "Method 'Select' of Object 'Series' Failed", and the code worked perfectly before. I'm completely lost now...
View 9 Replies
View Related
Aug 1, 2014
For some reason I'm consistently getting an error in the following line:
Code:
Public wkbk1 As Workbook
Public shtInput As Worksheet
Public i As Integer, iPass as Integer
[Code]....
View 6 Replies
View Related
Mar 6, 2008
At my company we need to print our workbooks containing 3 to 24 worksheets. The first page should be printed on different paper than the other pages. From various forums I gathered that it's not possible to set the papertray using vba. Those forums suggest 3 things: 1. Windows Api functions (don't work at our company(security)), 2. SendKeys (don't work because of different printers, office versions and future-proofness) and 3.
Define printerdrivers per tray.
This last I did, so there are printerdrivers for tray3 and tray4.
With the following code I try to print the workbook:
Public Sub printSheets(strP1 As String, strP2 As String)
Dim curPrinter As String, firstPage As Worksheet, otherSheet As Worksheet, x As Integer
Set firstPage = Application. ActiveWorkbook.Sheets(1)
curPrinter = Application.ActivePrinter
Application.ActivePrinter = strP1
firstPage.PrintOut
The problem: The sheets are sent to the printers specified as strP1 and strP2, but the tray-settings from these printers are "overruled" by Excel; they are printed from the papertray that was the default of the printer when Excel was started.
Also, using the default printer dialog from Excel has the same problem; when the (windows-)default printer is Tray3 then all the pages come from there, even when printer Tray4 is selected as the printer. In Word or Acrobat etc the prints come from the right tray.
View 3 Replies
View Related
Jan 28, 2013
I have a two identical worksheets in the same workbook with two tabs (Search, Database). The Search worksheet contains a search engine at the top which searches by column, and pulls rows from the Database tab, which match the search queries. However, the rows that are pulled up in the Search tab cannot be edited as they are all MATCH formulas. Only the rows in the Database worksheet can be edited.
This can be quite problematic if I am searching a row to edit it. Although the search engine is convenient enough and faster than filtering (as I have over 20 columns), when the row comes up, I cannot edit it. I have to go to the Database sheet and filter through until I find that row, and THEN edit it. What I require is that when I search for something and the matching rows get pulled up, I want to be able to click a button which will take me to those rows in the Database sheet.
View 14 Replies
View Related
May 19, 2008
The following line of VBA code executes properly if machine settings are US English, but throws a "Run-time error '1004': Application-defined or object-defined error" ? ...
View 9 Replies
View Related
Aug 12, 2009
I am trying to copy the print settings(including the headers and footers) for one excel worksheet to another. I have found the following hint on internet and it works fine for copying the print setting(including the head/foot) but it is also grouping the worksheets. Click on the tab of the worksheet you want to copy. Then hold down the Shift or Ctrl key and click on the tab of the worksheet where you want the settings and click on File, Page Setup and OK. How can I just copy the print setting without grouping the worksheets?
View 4 Replies
View Related
Oct 8, 2009
In Excel 2003 VBA, what is the syntax for controling an Option Button or Checkbox by name or caption
I have tried the following but has an error, I am sure this is a simple one.
View 4 Replies
View Related
Mar 7, 2014
How do I change the print settings of individual pages within a worksheet. Specifically, I'd like my worksheet to print out 4 pages, the first as portrait and the remaining as landscape.
View 2 Replies
View Related
Dec 10, 2013
I'm trying to replace a bunch of checkboxes on a sheet with a bunch of Form Control Option Buttons. The problem I'm having is every option button I add seems to be grouped with all of the rest of my option buttons. If I add buttons 1 & 2, I need those 2 grouped together, and when I add 3 & 4, I need those grouped together. Currently it's grouping all 4 buttons together so 1 - 4 are linked, but I need 1 & 2 linked together, and 3 & 4 linked together. I've tried grouping, but it still doesn't function how it ought to.
View 13 Replies
View Related
