Re: Trying to use global variable
Thank you very much. My problem is solved with your help.
I thought about it but didn't knew where to declare variable.
Re: Trying to use global variable
Thank you very much. My problem is solved with your help.
I thought about it but didn't knew where to declare variable.
Re: Trying to use global variable
Quote from StephenR;751257Why are you assigning a value to 'a' twice - the active cell and the target. In the Change event it will assume the value assigned in the SelectionChange event.
because I try To make code that if I change value In A2, A3 will get value that A2 had before. For that I need to save A2 value in "a" before it changes when A2 is selected, but if A2 SelectionChange event doesn't occur, because A2 was already selected when file was opened, then I need to save A2 value on workbook.open event
In workbook module I have this code, where I declared Global variable "a"
In sheet1 module I have this code where I'm trying to use declared variable "a"
Public Sub Worksheet_SelectionChange(ByVal Target As Range)
a = Target.Value
End Sub
Public Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A2")) Is Nothing Then Me.Range("A3").Value = a
End Sub
but code in sheet1 module do not considers "a" as the variable declared in workbook module.
Please tell what I'm doing wrong and how to do right
Re: VBA to Place Current Month and Year in a Cell
Range("A2").Value =Month(date)&" "&Year(date)
Re: VBA - Autofill form based on search for editing
I've edited code above. This code will find in case of exact match. And this code should work if you use only userform1 and add to it searchButton.
I have not enough time know to make complete file for you. but i think I showed you good way to solve your problem yourself and in better way.
You can do it
for checkboxes use codes like this(it's for one checkbox "admissionCheckBox") and put them before ENd if into the code above:
Re: VBA - Autofill form based on search for editing
I think for your purpose only one userform is enough. for example if you add search button to first userform, it'll be best solution. Also i'd use headers it would be better i think.
For the search button use "find" property on column C where you put organisation names(I'd use column A for this), after finding searched cell using offset property you can extract data to sufficient textboxes
like this to extract sufficient values for two texboxes. In this case you have to use headers, because "find" property doesn't look in range's first cell.
Private Sub searchButton_Click()
'Search and display
With Sheets("Sheet2")
Dim r As Range
Set r = .Range("C:C").Find(orgApplicantTextBox.Value)
if r is nothing then
MsgBox ("Not found")
Else
dateTextBox.Value = r.Offset(0, -1).value
contactPersonTextBox.Value = r.Offset(0, 1).value
End if
End With
End Sub
Display More
may be instead of .Range("C:C") it's better to use .Range("C1",.Range("C1").xldown)
Re: Microsoft office excel has stopped working on saving
I used userform on every sheet where it can be used, and saved file. After that problem was solved. So I can't understand how it could be problem with code, If the same code works perfect now after doing what i mentioned.
But i still want to understand what was the problem in. I can post codes if it'll be enough to find out what was wrong.
Re: Microsoft office excel has stopped working on saving
I'm sorry but I can't upload this file it contains private information. And I'm almost sure that there is no problem with code.
I found out now that if I save and close file after using userform and then reopen it and run button macro everything is ok. In this case I can save my file with final data.
Hi all
I have excel file with userform, buttons and macros for them. It works nice but problem occurs only when i use userform(it adds data to cell), then run button macro (macro also runs nice), but then if I try to save file
"Microsoft office excel has stopped working" alert occurs.
I'm messed because all codes run well, on end file is ready but i cannot save it.
Have you any ideas what can cause problem???
I found out now that if I save and close file after using userform and then reopen it and run button macro everything is ok. In this case I can save my file with final data.
Thank you.
Re: create copy of workbook and clear some data in created workbook
Thank you for your reply.
I've already corrected it by changing SavAs on SaveCopyAs and it works.
But your code is smarter.
If I knew that in this case new workbook was an Activeworkbook I'd also use With ActiveWorkbook instead of this long rows of code.
Thank you again. I study much using your examples.
Re: IF two criteria are met in the row THEN SUM but, only if it is between a date ran
I think with out sample workbook it's not clear what you want to do.
Are number of rows of columns(A,B,D,J) constant? Or they can vary.
what do you mean in "If column A contains CDS". Is it "if any cell's value of column A equals to CDS" or you need also cases when cell's value contains CDS?
I need a macro on button click that will create copy of active workbook, clear some cells in first 3 sheets of this copy file and save it. Before saving it also removes filtermode on sheet "sul".
I wrote this code. It creates copy of workbook but doesn't clears cells at all and doesn't removes filtermode in copy file. After code execution copy file is open and source file is closed.
Please help to correct it. Thanks.
Sub Button1_Click()
saxeli1 = "00.00." & Year(Date) & ".xlsm"
If Len(Dir(ThisWorkbook.Path & "\" & saxeli1)) = 0 Then
ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & saxeli1
Workbooks.Open (ThisWorkbook.Path & "\" & saxeli1)
For i = 1 To 3
Workbooks(ThisWorkbook.Path & "\" & saxeli1).Sheets(i).Range("D3:CY202").Clear
Next
Workbooks(ThisWorkbook.Path & "\" & saxeli1).Sheets("sul").FilterMode = False
Workbooks(ThisWorkbook.Path & "\" & saxeli1).Close SaveChanges:=True
End If
End Sub
Display More
Hi all
I need such formula, that if I write in A1 another worksheet's name(for example worksheet2) and in A2 it's sheet's name (for example sheet1) formula will return value of A3 cell from sheet1 worksheet2.
I don't want to use VBA, and I want to do this without opening of worksheet2. (So I can't use INDIRECT function)
Can somebody help?
Thanks for your time
Re: Calculations(substraction) with text boxes and labels
Thanks for the reply but
Did you tested what you advised?
It causes "run time error 13 type mismatch" after first keypress
Re: Calculations(substraction) with text boxes and labels
I found this http://support.microsoft.com/kb/214118/en-us and seems this is only way to solve this problem , I have to use Round function. But does it mean that Round function is needed every time I make calculations, to be sure that calculation result will be correct? It's annoying me.
May be there is better way to deal with it? I need exact answer. Can somebody give it?
Hi
I' have searched many about this but didn't found proper answer.
I made and attached example workbook with code given below. It works fine in most cases but you can see there that for example, if Textbox1 is 17.02 and textbox2 is 17.01 subtraction gives 9.99999999999801E-03. but I need simply 0.01. How can I solve this?
Private Sub TextBox1_Change()
Label5.Caption = Val(TextBox1.Value) - Val(TextBox2.Value)
Label6.Caption = Val(TextBox1.Value) + Val(TextBox2.Value)
End Sub
Private Sub TextBox2_Change()
Label5.Caption = Val(TextBox1.Value) - Val(TextBox2.Value)
Label6.Caption = Val(TextBox1.Value) + Val(TextBox2.Value)
End Sub
'Restrict Textbox1 and Textbox2 to number with max 4 digits before comma and max 2 digits after
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case Asc("0") To Asc("9")
If (TextBox1.SelStart = 1 And TextBox1 = "0") Or (TextBox1.SelStart > 3 And InStr(1, TextBox1, ".") = 0) Or _
InStr(1, Right(TextBox1, 3), ".") = 1 Then
KeyAscii = 0
End If
Case Asc(".")
If TextBox1.SelStart = 0 Or InStr(1, TextBox1, ".") > 0 Then
KeyAscii = 0
End If
Case Else
KeyAscii = 0
End Select
End Sub
Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case Asc("0") To Asc("9")
If (TextBox2.SelStart = 1 And TextBox2 = "0") Or (TextBox2.SelStart > 3 And InStr(1, TextBox2, ".") = 0) Or _
InStr(1, Right(TextBox2, 3), ".") = 1 Then
KeyAscii = 0
End If
Case Asc(".")
If TextBox2.SelStart = 0 Or InStr(1, TextBox2, ".") > 0 Then
KeyAscii = 0
End If
Case Else
KeyAscii = 0
End Select
End Sub
Display More