Excel 2007 :: Clicking Checkbox In Oracle Form Using VBA
Jun 7, 2012
One of my engaging tasks at work is to disable/enable general ledger accounts (accounting speak) in our Oracle ERP application using one of their forms.
In this case it would be a list of gl accounts and to the left of the gl account would be a check box that I can click.
Checked means enabled.
Blank would mean disabled.
If I have a list of currently enabled gl accounts that are to be disabled (unchecked) then I have to do so one by one. I can click with the mouse on the checkbox or I can using the keyboard use the space bar followed by the down arrow key and repeat. As fun as that sounds sometimes I'm faced with dozens or hundreds at a time.
Therefore my question is can I automate this using VBA? or any other tools out there.
Using Excel Office 2007, Oracle ERP (9.5.8) I think - it's old
I am running Excel 2007 and connecting to an Oracle DB.
I have created a VBA macro to connect to an oracle DB and retrieve data then place it in a table. The code works, but it uses DSN entries which are specific to my computer. I want to be able to distribute this Excel spreadsheet to others in my company and have them be able to click one button and update the data. I figure the most logical way is to connect to the DB using an IP address that should work for anyone on the intranet.
How do I modify the connection info below to have it connect via IP?
I tried "Data Source = 10.1.1.10orcl" and "Data Source = 10.1.1.10", both of which VBA dislikes.
Here is what I have now:
Code: Sub Create() Dim Servername As Range Set Servername = ActiveWorkbook.Sheets("Summary").Range("B16")
Set StartDate = ActiveWorkbook.Sheets("Summary").Range("B3")
In any open (new or existing) workbook (Excel 2007), I cannot delete a column by right clicking the column header and highlighting the entire column. The "delete" word is greyed out and not selectable. I CAN however still delete a column by going to the "Cells" toolbar, then "Delete" pulldown menu, then "Delete Sheet Columns". Right clicking the column header is much simpler and easier.
BTW, I can still delete rows by both methods of right clicking on the row header and by going to the Cells toolbar.
Is there a way to freeze your pivot table format after clicking for a ' Refresh'. I'm getting tired reformatting my column headers to wrap or in the middle etc. I'm working in Excel 2007.
What would be the quickest and most efficient way of doing the following:
Example
Sheet 1: clicking once, twice, three times in the following cells to change the fill to Red, Yellow, Green respectively (in Row 22, COLUMNS E H K N Q T W Z AD AG) and mirrorring this format/fill on:
Sheet 2: , mirror the selected fill on sheet1 in E22, H22 , K22 in in E3:N3 respectively example E22 (sheet1) is mirrored now in E3 (sheet 2); H22 (sheet 1) is mirrored in F3 (sheet2) etc etc.
I have 35 tabs formatted in the same way - each tab, as with Sheet 1 is linked to a different row on sheet 2 in the same range as is sheet 1 and would need to replicate the mirorring on Sheet 1 to each of the other 34 tabs.
I have an Excel 2010 workbook with many worksheets using hundreds of Form Control (not ActiveX) checkboxes. I need a bit of VBA to change Checkbox background color of each checkbox whenever the user checks the box. I assume this needs to be a click event? I don't even know the Checkbox property name I need to change I'm learning VBA as quick as I can
I'm trying to get a checkbox in a user form to prepopulate depending on what's in cell H5.
Here is the line of code that I need rewriting (in bold):
Sub Userform_Initialize() LabelPolicyNumber.Caption = ActiveSheet.Range("B5").Text LabelSponsorName.Caption = ActiveSheet.Range("D5").Text If Application.WorksheetFunction.IsNA(ActiveSheet.Range("H5")) = True Or ActiveSheet.Range("H5") = "" Then CheckBoxHalifax.Value = False Else CheckBoxHalifax.Value = True End Sub
H5 contains a vlookup formula, so depending on other variables it can either have a value ("Halifax"), an error (#NA) or be blank. I've
It seems Excel will only evaluate the first statement and ignore the Or statement, meaning when H5 is blank Halifax is checked off when I load the user form.
I'm building a spreadsheet which includes a few columns which have activex check boxes which are linked to cells. I want to allow the users of the spreadsheet to insert new rows if necessary. Ideally, when these new rows are inserted, the check boxes would also automatically appear in the corresponding columns of the new row (and be linked to the relevant cells), just the same way that pull-down menus and formulas automatically copy into the new row. Is there a way to do this?
I currently have the following Macro for one of my many checkboxes in 2007 Excel:
[Code] .....
It works perfectly until additional rows are added/deleted before the indicated rows in the code (It changes the number sequence in the workbook). The number sequence stays the same in the code which means I am now hiding rows either before (delete rows) or after (insert rows) the intended rows I want to be hidden. Is there a way to change the above code to remain with the assigned rows regardless of the adding/deleting of rows before it?
Whenever she clicks on a cell, the cell to the right of it is also selected. When she tries using tab to move to a new cell, she can only move between the two selected cells. Same with using the enter key. As such, it is extremely difficult for her to modify only one cell, since she always has two selected.
The F8 key, as well as Ctrl+F8. However, pressing the F8 key only adds more cells to the autoselection, and Ctrl+F8 allows her to select one cell, but also highlights the cells around it, and when we tried to select other cells, every cell we clicked stayed highlighted.
Additionally, sometimes when she clicks a cell, it will just select that one cell. Click it again, and the problem is back. I haven't been able to determine any patterns to this behavior, and I know there is no problem with the input (the keyboard and mouse are standard-issue in our lab, and we keep them well maintained).
I want to link a table to a form. Only, I noticed that when setting the input range in the form I'm unable to use OFFSET OR a table name. How can I link the form to a list which is going to expand?
Attached is an example. Two sheets. One for the list. One for the form. The list will keep getting added to.
Unrelated-but perhaps useful - Is it possible to link one cell to a list in another sheet so a user can type in a word and it will offer autocomplete options in excel 2007 ?
I have a sheet with 500 rows and 20 columns. I want to put an input box and a button on a different sheet. What I would like to happen is that when you put the criteria in the input box and click the button it opens the form for the row corresponding to the criteria and allows you to edit the 20 columns. Excel 2007.
We just upgraded to Excel 2007 from Excel 2000 and have run into a challenge relating to labeling a form command button. It appears that the length of text for a command button label has been shortened to 31 characters when setting the name using VBA. No such limitation shows up when I manually create such a button.
Following is the code that used to work to create the button and label in VBA (Excel 2000):
I have this worksheet with 2 buttons and 2 textboxes.the first button is named search literally to search the items inputted in the textboxes and the second button is clear which clear all the inputted items in the textboxes and the search field. I manage to code those things however I have a main problem. How am I suppose to linked a checkbox with those items?
For example I searched this particular item so the checkbox corresponding to that item must show up in the left most column of it. and if I searched multiple items those checkboxes must show up too. I am having a hard time coding and trying since I am new in vba. I also attached a sample worksheet. I am using excel 2007.
I'm trying to create a very simple order form. It's been a while since I've done this in Excel, and I couldn't find an answer when I searched. (I may be a lousy searcher, though.)
I want the user to enter a quantity in Column E of the "Common Items" worksheet, and have all the rows with quantities copied to the "Order" worksheet. (On the "Order" copy below, I just did a copy/paste to show the desired effect.)
I'm pretty sure this can be accomplished with an If/Then, but I'm lost! Optimally, they'd enter their quantities, click on the Order sheet and hit print.
I'm Using Excel 2007 and would like to have some VBA to work with the following!
I have a simple pivot table (PivotTable1) in Sheet1 with three items in the Report Filter which has been named "ROUTE" I have created a ComboBox in Sheet2 and have added the identical three items in via format control, cell link A1.
I would like to be able to use the combobox in sheet2 to operate the PivotTable Report Filter in Sheet1 as I would like to build a report whereas a user. Can only select the comboBox and does not see the pivotTable
Sounds simple but cannot get this to work no matter what I try.
I have many Form Control Check Boxes that all link to another sheet on row 3.
I have many changes to make but only want to implement the change related to the check box.
This code works perfect when you manualy type true or false on row 3 but not if the check box makes the change.
Private Sub Worksheet_Change(ByVal Target As Range) ThisCol = Target.Column If Target.Row = 3 Then RESULT = MsgBox(Cells(1, ThisCol) & " = " & Cells(3, ThisCol), vbOKOnly, "CLICK RESULTS") End If End Sub
Why does this not work when a check box changes the value in row 3?
I want to have a Checkbox (Form) that is in my Worksheet to disable/gray-out once it is selected. I don't want people to be able to uncheck it again. I want them to be forced to click another Checkbox to enable it again.
Example:
Two Checkboxes:
Check Box 1 = "Apply" Check Box 2 = "Delete"
Once "Apply" is checked, gray it out. This will force a user to click "Delete" in order for the "Apply" button to be enabled again. I do not want a user to click "Apply" once selected as their way of 'deleting' the information.
I've got one more problem to solve with my current project. An example of the workbook is attached.
What i want to do is change the code on my 'enter button' of the 'input form' which can be shown by clicking the 'Add New Hedge' button on the summary sheet.
The change i want to make is when the user clicks 'enter', if the 'settle now' checkbox is false to add the details of the form to the unsettled hedges form as it currently does.
If the 'settle now' checkbox value is true then i want the form details to be added to the next available row on 'settled hedges' worksheet, in the same way it currently does for adding into the next available row of the 'unsettled hedges' sheet. Also here, i would want the value of the 'returns' textbox added to be added into column L rather than the text "unsettled". This would also mean when the 'settle now' checkbox is true that the 'returns' textbox needs to have a value before adding any details.
I really don't know how to code a checkbox correctly. On the sheet, I have a form with CheckBox1 and Checkbox2. I would like Checkbox2 disabled until Checkbox1 is checked. Also, when Checkbox1 is checked, display Image1, otherwise Image1 is hidden. When CheckBox2 is enabled and checked, display Image2, otherwise Image2 is hidden. I know this is real beginner stuff, I'm still a rookie.
I have data in range J2:J365 , H368:H401 & J403:J827. i want to check wether this range have negative values or not if yes load all negative values in the listbox1 by clicking checkbox.
i use excel 2007, it s slow to open a file by double-clicking and entering from windows explorer, by googling i fould a fix by adding "%1" at the end of command in "Application used to perform action" (folder options/file types/xls/advance/open). however, to open files in the same instance, i have to keep "DDE message" with [open("%1")]. With both "%1", excel now opens twice. How to fix it?
I am simply trying to write a macro or code that will set all the check box values to "False" each time I initiate the User form. So that the user form does not "carry over" check box values from the last time the User form was used.
However I had to make sure I cleared the value from the cell that each control is associated with.(the controlsource in the user form properties.)
I have coming from the Forms Controls within a Sheet 6 CheckBox.
for the following countries: "Spain", "France", "UK", "Italy", "Germany" and "EUROPE"
when Europe is selected the I select all the Check Boxes at once by Macro, if Europe is selected and I unselect Europe, then I unselect all the countries by Macro.
When Europe is selected and then I press for eg in Spain, the Spain is not selected and then I unselect Europe, by macro.
This works fine, and my macro does the job OK.
Code:
Sub mCountries() On Error Resume Next: Application.ScreenUpdating = False Dim wCaller As String: wCaller = Application.Caller Dim aCheckBoxes(): aCheckBoxes = Array("cBoxSpain", "cBoxFrance", "cBoxItaly", "cBoxGermany", "cBoxUK") Dim X As Long: X = -4146 Dim Y As Long
[Code]....
But if My 6 CheckBoxes are grouped... the Macro does do the job How can I refer to a shpes withing the Grouped Shape to apply the selection and/or to unselect??
I have a multi-worksheet workbook that has many forms control checkboxes throughout it. I'm looking for some VBA that will change the background (fill) color of ALL the checkbox when it is checked (True). I've seen code for a single checkbox, but not multiple/all boxes. I know just enough VBA to be dangerous, but I'm up to learning anything new.
I found in an old file that i triggered the macro by clicking a button! I would post a pic but i don't know how. How do I activate a macro by clicking a shape on the excel 2002 page?
how to run some VBA code (written by someone else, unfortunately) only when clicking once in cell A1, and not run in any other cell. This is being run in Excel 2010. The code I would like to run in this manner is below, and currently will run when the user clicks on any cell in the worksheet it is applied.
[ Option Explicit_________________________________________ Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'If IsDate(Target.Cells(1, 1).Value) Then Set DatePickerForm.Target = Target.Cells(1, 1) DatePickerForm.Show vbModal Cancel = True 'End If End Sub ]
My cells have wrap around text enables. However when I double-click the row, it does not automatically adjust the row height to accommodate all of the text. It shrinks to a small row height. How do I fix this?
