I am trying to create a macro (please look the attached excel what I have done), I have already crwated a macro that after 5 seconds is gonna change the background color of the of the cell en function of another cell.
That means if F3:F4 change the background color F8:is going to change the backgorung color also, every 5 seconds. The macro I wrote is also include in the excel attached file.
Now this is my problem... I have a variable cell that is give for D6 which could be A,B,C,D,E,F (Range F2:K2).
If the variable cell D6 is A the background color of F8:F9 should be the background color F3:F4 .
If the variable cell D6 is B the background color of F8:F9 should be the background color G3:G4 .
If the variable cell D6 is C the background color of F8:F9 should be the background color H3:H4 ......and etcetera.
Option Explicit Sub Disney_DumpData() Dim wbBook As Workbook Dim wbNew As Workbook Dim ws As Worksheet Dim wsNew As Worksheet Dim rng As Range Dim rngNew As Range Dim rngUnit As Range Dim rngUnitPaste As Range Dim lngNew As Long Dim Cnt As Integer Application. ScreenUpdating = False Application.Calculation = xlCalculationManual Application.DisplayAlerts = False Set wbBook = ThisWorkbook Cnt = 0 'Dump To New File '/Define new workbook Set wbNew = Application.Workbooks.Add.....................
I have a macro that nicely select the named range that the active cell is in. I want to chain on to that macro a macro that has a parameter a range with that active selection. I dont see how to "take" the active selection on the worksheet from within the macro and pass it to another. I assume I could change the cellInRange macro to return a range, however I dont yet see how to do that.
I am using this program in one workbook to capture the datevalue in integer from another workbook which i opened. But the program as it reaches the line x2=Datevalue( Cells(2,14).Value) gives a Type Mismatch error.
Public Sub find_date() Dim x2 As Long
'I am trying to activate the last opened file by using workbooks.count Workbooks(Workbooks.Count).Activate Worksheets("Sheet1").Cells(1, 1).Select
x2 = DateValue(Cells(2, 14).Value)
Auto Merged Post Until 24 Hrs Passes;btw..the cells(2,14) has a date, formatted in the type of mm/dd/yyyy.
The above Date si used in a sql select statement where I'm hard coding the date (9/20/2007). This date is actually located in sheet1, cell E1. How can I get it from that cell and use it in my Select statement instead of entering the date manually every day in my code?
How do I use VBA to send the value of a cell, for example, cell J77 to the variable "mastervalue1"?
I have a spreadsheet that has a column with dates ( in the format "mmm-yy") and next to it a column that has values. I want to pass the value of the cell that is next to the date cell that contains today's month to a variable in VBA. Auto Merged Post;here's an example of my spreadsheet
The first goes through a directory and opens all the files.. after it opens a given file it goes off into a sub-routine to process the data in that file.
I am trying to create a counter in the first sub-routine and then pass that value into the second sub-routine to tell it to put the values out on the next row down.. so the first time through it puts the values out on row 1, next time it puts them out on row 2, etc.
I have uploaded a copy of the spreadsheet I am working on and have completed the user forms and coding for march the 1st only, so if you test, please use the options march and then the 1st!
The problems i have is, when I enter a number into a text box and press next page, the numbers fill where i want them to but the cells do not recognise them as numbers, therefore conditional formatting doesnt work! Even if I change the cell properties to numbers, this does not remove the error!
Second problem!! If a user forgetts to enter a value in a box, or they wish to edit just one value, and go back into the user form to change a figure, when they press next page, all the values in the column seem to disappear!
I use the code below to enter a value from a list box in a cell on a workssheet. Is it possible to code VBA to enter a number for the position of the selection in the listbox to a cell in a worksheet rather that the actual value from the list box. For example if my list is:
Option1 Option2 Option3
And I click on Option2 in the list, I can sennd the value 2 to a cell on the worksheet rather that the value "Option2' from the list.
Private Sub ListBox1_Click() Sheets("SA").Range("SA_Poistion_To_Archive_A_New").Value = ListBox1 End Sub
I am building a Workbook which takes data from SheetA and inserts it into SheetB. Part of the data is only entered when a positive value exists.
I then do an export from SheetB.
The problem is that I need to get the column number and pass it to the cell reference based on the field name in row 1.
Dim sFindstring As String Dim rFindcell As range Dim iR As Integer Dim iC As Integer
sFindString = " Find this string in the cell" 'Using cells find the findstring Set rFindCell = Cells.Find(What:=sFindString, After:=[A1], LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
'OK so look here iR = 3 'I am trying to pick up the column number iC = rFindCell.Column
rFindCell throws an object or with block variable not set error. Is there some property that I need to set.
I am calling a sub that I want to do a SaveCopyAs using a variable as the file name but can't get it to work. The file name displays properly in a message box but when I do a SaveCopyAs it does nothing.
I have code in a userform that shows another userform with a text box. When the user types text there and presses OK, I want that userform to close (which it does fine), and return back to the original userform (which it does fine), but I want the typed text to be stored in a variable that the code in the original userform can use.
I tried declaring a public variable, "Rresponse1", in each userform's procedure, but that doesn't work. I test for the content of the variable afterward in the original userform and it is empty.
In the original userform I have:
SaveList.Show '(this is the second userform) MsgBox "This is response1: " & Response1 '(to test if the variable is being passed)
In the userform SaveList, in the Private Sub CommandButton1_Click() procedure, I have:
Response1 = SaveList.ListName.Text Application.ScreenUpdating = True MsgBox Response1 '(to test the variable, here it shows the text fine) Unload SaveList
How do I get the Response1 variable to keep its content when control is passed back to the original userform?
I am trying to pass two variables RARD and CARD from Sheet1 'change selection' subroutine to the 'Set ARD Command button click' subroutine. The variable value remain empty and it gives me a 1004 error when I try to execute this line of code:
What I am trying to do is put the text value 'ARD' in the cell on sheet1 which was the original cell I clicked on to open the userform2. To do this I click on the 'Set ARD' button on the userform2 which is then suppose to hide the userform2 and put the 'ARD' text in the selected cell on Sheet1.
I have attached the workbook for review : Therapy Tracker - Tester V2.10 - deleted logo.xlsm
I have a spreadsheet with about 30 charts on it that I would like to attach a macro to which opens the chart in a form. I've figured out how to do this last part, but am stuck on how to pass a variable to the form code which tells excel which chart to copy. Essentially what I'm doing creating a macro for each chart which would run when that chart is clicked on. This macro is identical for each chart except for the name of the chart being passed.
Sub Chart1_click() ShowChart "Chart 1" End Sub
Sub ShowChart(c As String) frmChart.Show End Sub
In the UserForm_Initialize code located in the userform module, I'd like to call the code which saves and loads the image of the clicked on chart. But here's where I'm stuck. How do I pass c to the code in the form module...e.g, how to pass c to UserForm_Intialize and to ChartZoom? Here's what I have so far...
Private Sub UserForm_Initialize(c As String) ChartZoom c End Sub
Private Sub ChartZoom(c As String)
Dim Cht As Chart Set Cht = ActiveSheet.ChartObjects(c).chart Dim CName As String CName = ThisWorkbook.Path & "cht.gif" Cht.Export Filename:=CName, FilterName:="GIF" imgCht.PictureSizeMode = fmPictureSizeModeZoom imgCht.Picture = LoadPicture(CName)
I have a macro that when it runs presents a form that gives the user an option to skip the calculations or continue. If they click Skip then it continues the 'Next CE' . My problem is I can't get the Boolean value to pass from the form to the macro. Here is what I have
The main macro
Sub Dividend_MF() For Each sh In ActiveWorkbook.Worksheets( Array("G&I", "Growth")) For Each ce In sh. Range("a5:a" & sh.Range("a65536").End(xlUp).Row) If Not IsEmpty(ce) And Not (IsError(ce.Offset(0, 56))) Then If ce.Offset(0, 56) = "X" Then
I have a code below which need some input from user. This input will also be serve as the input of the subroutine which i am going to call. However, i do not know how to go assign this input to the subrountine which i will be calling, can anybody help ?
For example, the "input" variable will also be served as an input in subroutine test2 ...
This is the code after editing to make it more clear
Public Sub 1() BookA= activeworkbook. name BookB=Application.Workbooks.Add Workbook(BookB).activate End Sub
Public Sub 2() BookB=Activeworkbook.name
With BookB. sheets(1) .range("A1")=BookA.sheets(1).range("B1") End Sub()
At the end of public sub 1, BookB is the active workbook. What I want to do in public sub 2 is to copy some data from BookA to BookB. Unfortunately, when moving from public sub 1 to public sub 2, BookA needs to be defined again. The code above is the code that I use in my add-ins. I figured out for non add-ins code I can define BookA with thisworkbook.name when BookB is active as I before work with BookA. This does not apply for add-ins as thisworkbook will refer to my add-ins code. Is there anyway of keeping definition of BookA is constant from one public sub to another public sub? This is simplified code. In fact, I can't merge public sub 2 with public sub 1 due to some reason which I don't say it here.
I am trying to pass information that is filled by user in a userform into an excel sheet. Let's say a user would click on a control button in a userform and Macro would ask him what value to store for the first variable. If user clicks one more time then Macro would identify that it was a second click and ask what value to set for a second variable. It is easy to do with limited number of variables, but is it possible that the variable which stores a number of clicks would become a number for variable to store the value?
1 click - a1 = .. 2 click - a2 = .. .... n click - an = ..
I'm just starting to experiment with passing values between workbooks and between modules and so far I've managed to get it working. However, I now can not run the receiving module independently because of the passed value ? Let me explain ...
I use start/end dates within my modules to create date sensitive reports. I have several files (for different departments) and each file has a module called "AbsenceChecker", I have been working on a way to create a Report Master workbook to run and amalgamate the "AbsenceChecker" module from each department.
Sub RunAllLoaders() Dim AbsenceStart As Date, AbsenceEnd As Date Dim PassVar1 As Date, PassVar2 As Date Dim DateStart As Date, DateEnd As Date On Error Resume Next 'start of data validation script continued in private sub. GetValidDates FromDate:=PassVar1, _ ToDate:=PassVar2, _ MinDate:=DateSerial(2007, 1, 1), _ MaxDate:=DateSerial(2007, 12, 31) Excel.Application.EnableEvents = False Workbooks.Open Filename:="R:RostersRosterALPHA.xls" Application.Run "RosterALPHA.xls!AbsenceChecker", PassVar1, PassVar2 End Sub...................