Re: return blank in cell
Try
=IF(A1*B1=0,"",A1*B1)
Re: return blank in cell
Try
=IF(A1*B1=0,"",A1*B1)
Re: Check if all Checkboxes are FALSE and send MSGBOX
You are welcome, I am sure Roy would have supplied something neater if it hadn't been the middle of the night for him!
Re: Check if all Checkboxes are FALSE and send MSGBOX
Maybe
Private Sub cmdPrintContracts2_Click()
Dim oCtl As msforms.Control
For Each oCtl In FrameCU.Controls
If TypeName(oCtl) = "CheckBox" And oCtl.Value = True Then GoTo Checked
Next
For Each oCtl In FrameB1.Controls
If TypeName(oCtl) = "CheckBox" And oCtl.Value = True Then GoTo Checked
Next
For Each oCtl In FrameB2.Controls
If TypeName(oCtl) = "CheckBox" And oCtl.Value = True Then GoTo Checked
Next
MsgBox "You haven't selected any forms to print"
Exit Sub
Checked:
Application.Run "PrintContracts"
End Sub
Display More
Re: Trim not Working
TRIM removes all spaces from text except for single spaces between words
If 14845ALAT 2013 is in A1 then =REPLACE(A1,10,1,"") in another cell will return 14845ALAT2013.
You can use REPLACE in your formula, but note that it will only work if the space is always going to be the 10th character in the string.
Re: Identify and number Patterns
Just do ask the moderators have asked, once you comply with the forum rules you will get the help you need.
Reply to this with links to all posts you have made regarding this problem, that means links to posts on this forum as well as any other forum you have used.
Re: List of GBP values not sorting correctly
Sorry forgot to attach file!
Re: List of GBP values not sorting correctly
See attached file.
The code for the CommandButton is:
Private Sub CommandButton1_Click()
Dim rCel As Range
For Each rCel In Range("a1", Range("a1").End(xlDown))
Select Case rCel.Value
Case "London"
rCel.Offset(0, 1).NumberFormat = "$#,##0.00"
Case "Paris"
rCel.Offset(0, 1).NumberFormat = "[$€] #,##0.00"
Case "New York"
rCel.Offset(0, 1).NumberFormat = "[$$-409]#,##0.00"
End Select
Next rCel
End Sub
Display More
You may need to change the formatting code due to your locale settings. The easiest way to do so is to record a macro while formatting a cell to the desired currency.
You can also add more cities and their related currency symbols by adding extra cases to the procedure.
Sorting could also be incorporated in the same procedure.
Hope this helps.
Re: List of GBP values not sorting correctly
There is no need for further formulae or code. When sorting Column B expand the sortfield to include Column A. Column B can then be deleted, or the contents cleared, leaving Column A sorted with the original currency symbols in place, this will be true for whatever symbol is there.
Re: Run Time Error 1004
cytop is asking you to post the code you wrote when you tried to 'turn the forluma into a macro'
Re: Check Box
Pike's code is correct, if you want to run it from a command button then change the procedure name to Private Sub CommandButton1_Click(). You may need to change the '1' in 'CommandButton1' if you have other command buttons on the userform, or if you give the commandbutton a new name.
Re: Coding for multiple ActiveX controls
Thanks cytop, that has clarified things for me.
I have a sheet with ActiveX TextBoxes, ComboBoxes, OptionButtons and CommandButtons.
I saw the thread http://www.ozgrid.com/forum/showthread.php?t=180701 and this helped a great deal in working out what to do. I am more used to coding for controls on a UserForm and was trying to apply the same principles to OLEObjects.
Just a quick question re the solution in that thread, before I waste by time on finding out by trial and error!
Do I need to create a seperate Collection for each of the 4 controls and a seperate Class Module or can they all be included in the same Collection and then code in the Class module to determine the type of control?
On a side note what are the pros and cons for using ActiveX Controls as opposed to Form Controls, as far as I can see both achieve the same result but the ActiveX way requires more complex coding, so I guess I must be missing something that gives ActiveX an advantage over Form Controls.
Re: Userform Tabstrip, How to code for tab selected
Thanks, that sorted it.
I have a userform with a TabStrip containing 3 tabs.
The userform is used to input data into 3 tables on 1 worksheet. The userform has 1 TextBox, 6 ComboBoxes, 3 Option buttons and a CommandButton. When the CommandButton is clicked I need the data entered using the other controls to populate the table corrosponding to the Tab selected.
I am strggling with the VBA to determine which tab the user has clicked.
I hope this makes sense!
Any help or suggestions greatly appreciated.
Re: Does workbook exist error
Thanks for that. I found I needed to change <> "" to <> Empty, then it works perfectly.
I got the following code from the 'Tips' section on this site.
Sub DoesWorkBookExist()
'Written by www.Ozgrid.com
'Test to see if a Workbook exists
With Application.FileSearch
.LookIn = "C:\MyDocuments"
.FileName = "Book*.xls"
If .Execute > 0 Then
MsgBox "There is a Workbook."
Else 'There is NOt a Workbook
MsgBox
End If
End With
End Sub
Display More
I have modified it to suit my project re. file name and path, and added code instead of the msgBox but get the following error on
Application.FileSearch:
Time Error '445':
Object does not support the action.
I am using Office 2007. The VB Help documentation gives the same example as above (except that they
dim fs and then set fs = Application.FileSearch) and this produces the same error.
FileSearch does not appear on the intelisense menu for Application. but if typed as filesearch it does capitalise properly.
Any help greatly appreciated.
Re: VBA to randomise lists in 2 columns
Many thanks again. Just one point: The last line should be to resize range h2 not i2.
I have a list of names in Column C (starting in C2) and another list, which represents a pairing with the names in Column C, in Column D (starting in D2). I need to randomise the list in Column C, place the new list in Column H and retain the original pairing (C2 with D2, C3 with D3 etc.) in Column I.
I do have it working but in a long-winded way using code to randomise the Column C list then using Vlookup to place the correct pairings in Columns H and I
I am sure there must be a more efficient way of achieving this.
Any help greatly appreciated.
Re: VBA To randomly pair a list of names
Post removed and a new thread started.