hi all,
this code create a combobox on sheet1
& populates it with the values of
range(a1:c3), once u click the combobox
u see that the values are not displayed
in a multicolumn as they should instead
they all show in one column.
please find attached an example illustrating
what i already said.
ur help is really appreciated
Posts by amer
-
-
Re: Insert Line Feed At Intervals In A Phrase
thanx very much andy
thats exactly what i needed
Rgds -
Hi everybody,
Each cell in Range("A1:A2000") contains a remark, each phrase or remark is
Between 5 & 70 characters all written without line feeds (carriage return,i mean Alt Enter)
Just spaces between words.
What I would like to do in every cell is to force a line feed (Alt Enter) every 10 characters, and if the 10th character happens to be in the middle of a word I want the line feed to be inserted at the end of this word.
Note that the phrases are not necessarily multiples of 10.
i would really appreciate ur help
Rgds -
Re: Thisworkbook Module Instead Of Every Sheet Module
thanx very much now its pefect
-
Dear all,
In a folder I have 2 excel files the the first file called products & contains sheets with the names of each product, in each product sheet I recorded the names of some customers.
The second file is called customers & contains sheets with the names of each customer.
when both files are open & I am in the products file I want to be able to select a product lets say shampoo double click on one of the customers & automatically go to the specified customer in the customers file.
I inserted this code in every sheet of products file & things went fine:Code
Display MorePrivate Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) For Each wb In Workbooks For Each wk In wb.Worksheets If wk.Name = Target.Value Then MyWin = wb.Name End If Next wk Next wb Windows(MyWin).Activate With ActiveWorkbook.Sheets(Target.Value) .Select Application.Goto .Cells(1, 1), True End With End Sub
Instead of inserting this code on every sheet module (I might have 60 sheets) I tried to insert it
Just In Thisworkbook module but it didn’t work:Code
Display MorePrivate Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) For Each wb In Workbooks For Each wk In wb.Worksheets If wk.Name = Target.Value Then MyWin = wb.Name End If Next wk Next wb Windows(MyWin).Activate With ActiveWorkbook.Sheets(Target.Value) .Select Application.Goto .Cells(1, 1), True End With End Sub
Thanx in advance for any helpPlease find attached an example of the 2 files I have
-
Re: Go Back To Default
thanx very much kneejerk
its excellent -
hi all
i changed the default behavior of the
"insert comment" command by inserting these
codes, i thought that by closing & reopening excel
everything will go back to its default.
any help to what should i put in the Private Sub Workbook_beforeclose()
event so that i can get back to the default behavior.
thanx in advanceCode
Display MorePrivate Sub Workbook_Open() Set PopA = Application.CommandBars("Cell").FindControl(ID:=2031) With PopA .OnAction = "ChangeCmtShape" End With End Sub Sub ChangeCmtShape() Dim cmt As Comment Set cmt = ActiveCell.AddComment With cmt.Shape .Height = 90 .Width = 250 With .TextFrame With .Characters.Font .Size = 11 .Bold = False End With End With .Visible = True SendKeys " " & "+{home}" .Select End With End Sub
-
hi wigi
actually i posted the second thread
just to correct the mistake that i didnt
put code tags.
sorry for this inconvinience -
hi all
i changed the default behavior of the
"insert comment" command by inserting these
codes, i thought that by closing & reopening excel
everything will go back to its default.
any help to what should i put in the Private Sub Workbook_beforeclose()
event so that i can get back to the default behavior.
thanx in advanceCode
Display MorePrivate Sub Workbook_Open() Set PopA = Application.CommandBars("Cell").FindControl(ID:=2031) With PopA .OnAction = "ChangeCmtShape" End With End Sub Sub ChangeCmtShape() Dim cmt As Comment Set cmt = ActiveCell.AddComment With cmt.Shape .Height = 90 .Width = 250 With .TextFrame With .Characters.Font .Size = 11 .Bold = False End With End With .Visible = True SendKeys " " & "+{home}" .Select End With End Sub
-
Re: Rename Text Files
thanx very much guys
things r perfect now -
Hi all
In a folder I have several text files (each text file is a customer statement)
I get the files from the computer dept named randomly, I need to rename each
File to its customer ID number, each ID consists of 15 characters (including the dash characters) and is found on the 3rd row of the file (3rd row , skip one character
And count 15 characters ex : 0010-902514-422)
I found a code that renames the text files incrementally (1,2,3…..)
I need to modify the code so that instead of renaming the files that way
I rename them by their ID number (get the ID of each file while looping then rename
It by that ID)
One more thing, can I get the ID then skip 44 characters and attach the following 14 characters to the ID (ex : 0010-902514-422- Lebanese Pound) .
Attached is a folder containing an excel file with the code and 2 statements
Thanx in advance for ur helpCode
Display MoreSub RenFile() Dim arrFiles As Variant Dim intCounter As Integer Dim datFile As Date Dim strFile As String, strPath As String strPath = ActiveWorkbook.Path arrFiles = FileArray(strPath, "*.txt") For intCounter = 1 To UBound(arrFiles) strFile = arrFiles(intCounter) Name strPath & strFile As strPath & intCounter & ".txt" Next intCounter End Sub Private Function FileArray(strPath As String, strPattern As String) Dim arrDatabase() Dim intCounter As Integer Dim strDatabase As String If Right(strPath, 1) <> "\" Then strPath = strPath & "\" strDatabase = Dir(strPath & strPattern) Do While strDatabase <> "" intCounter = intCounter + 1 ReDim Preserve arrDatabase(1 To intCounter) arrDatabase(intCounter) = strDatabase strDatabase = Dir() Loop FileArray = arrDatabase End Function
-
hi all
in a folder i have an excel file and a text file
my code below does a simple task which is to import
the text file to the excel file.
when i execute the code on my personal notebook (stand alone
not linked to a network) things r ok, whereas when i
execute it at work where computers are linked to a network
the code runs whithout error but the data in the textfile is
imported blank (i get blank data on my excel sheet)
first i thought that the problem might be the path of the
textfile considering that i am working on a pc linked to
a network, but the fact that the code is not giving an error means that
the code is getting the full path of the textfile but
while importing the data something is going wrong
attached is my folder containing the textfile and the excel file
thanx in advance for any helpCode
Display MoreSub Import_Txt() FilPath = ActiveWorkbook.Path With Application.FileSearch .NewSearch .LookIn = FilPath .Filename = "*.txt" If .Execute() > 0 Then For i = 1 To .FoundFiles.Count FilName = .FoundFiles(i) Next i Else MsgBox "No files found" Exit Sub End If End With Set shFirstQtr = ActiveWorkbook.ActiveSheet Set qtQtrResults = shFirstQtr.QueryTables.Add _ (Connection:="TEXT;" & FilName, _ Destination:=shFirstQtr.Cells(1, 1)) With qtQtrResults .TextFileParseType = xlFixedWidth .TextFileFixedColumnWidths = Array(21, 31, 5, 21, 2, 200) .TextFileColumnDataTypes = _ Array(2, 1, 2, 1, 2, 9) .Refresh End With End Sub
-
Re: Object Required: Union Method
tahnx very much guys
-
Re: Object Required: Union Method
file
-
Re: Object Required: Union Method
sorry i forgot to attach the file
-
Re: Object Required: Union Method
thanx guys for ur replies
i modified the code according to bart but still its not working
attached is an example for illustration.
one more question isnt it normal to use the union method
to combine a range set to nothing with another range
say row 3 so that the result of the union would be row 3
thanx again -
hi all
columnA contains account numbers sorted in ascending order
i need to use the union method to select all rows where the
account number changes so that later i can insert rows between
different account numbers in one action.
i tried to use the following code but an error occurs stating
424 object requiredCode
Display MoreSub MyRows() For k = 1 To Cells(Rows.Count, 1).End(xlUp).Row If Cells(k + 1, 1) <> Cells(k, 1) Then Set bigRange = Application.Union(bigRange, Cells(k + 1, 1).EntireRow) k = k + 1 End If k = k + 1 Next k bigRange.insert End Sub
thanx in advance
-
Re: Show 2003 Instead Of 2007 Beta Version
hi andy,
what do u mean by detect and repair
thanx and regards -
Re: Show 2003 Instead Of 2007 Beta Version
thanx rpaulson for ur reply
and sorry for my late reply
i did as u told me step by step.
now on my excel files u can see
2003 written on the icon but when
i double click to open a file it opens
with the 2007 beta interface (i mean ribbons
and all new stuff) with one exception
i have 65536 rows and 256 columns
i really thank ur patience
regards -
hi all
i installed office 2007 beta version on my computer (for testing)
together with the already existing office 2003.
now the default program of my excel files is excel
2007 beta version (not excel 2003).
can i change this behavior so that when i open an excel file the
default program would be excel 2003 instead of excel 2007
and whenever i want to open a file using excel 2007 i do
so by right clicking the file & choosing excel 2007 from the
open with list.
i appreciate ur help