Posts by philby

Re: Change general error message - being lazy - Invalid Property value

OK - so being lazy was not productive so I fixed it with code - oh well!

Hi All - So it's a reasonably simple question, I've created a stock-take spreadsheet based on userforms in Excel for our SAP stock-takes - were batch managed so I have a combobox which is populated from the data held in 180K odd rows (it check for the code, then adds in the batch number if the code exists) anyway the properties of this were not set (so Matchrequired) was originally set to false - this was how we wanted it originally so a user could load in a batch that didn't exist in the branch as at that stage we were trying to get all the batches correct in the branches - we would simply extend them.

So now moving forward 2 years we have pretty much fixed all the batches on incoming shipments so now I'm changing this property to matchrequired = true - now this works fine no problem there but the error message is the generic MS - Invalid property value - Can I change this generic error message? - or am I forced to write more code (on error etc etc etc). Just trying to be a little lazy! - save writing more code!

Re: Using an array to replace for next loop

Thanks I'll have a play to understand how it works and come back to you.

Hi All

OK so I've written some code for stocktakes in SAP - basically it runs though a large row of cells, identifies batch numbers relevant to the data being entered and populates a combo box with these batches for selection

Now the issue I have is the delay in running the loop using a for next argument - I'm sure it would be faster in an array but can't figure out how to check the batches against the data entered into the textbox. In theory I only need to build the array once as the data is static and were only repopulating the combox.

So here is what I have currently - it works fine but I'm not happy with the delay, I found changing the properties of the various textboxes and comboxes on the user form caused repeated loops of the for next statement so have had to stop these manually.

The for next loop is running through around 65,000 rows - I've filtered this as much as possible without losing any data required.

Here is what I currently have

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
' to stop loops




TextBox1.Enabled = False
CommandButton3.Enabled = False


' wants to loop after properties changed
If t = 1 Then
t = 0
Exit Sub
End If


' reset local worksheet
Sheets("sheet2").Select


'set up range
Set sapcode = Range("B1", Range("B1").End(xlDown))


' clearing anythin in combobox
ComboBox1.Clear
ComboBox1.value = ""


' for messabe box
s = 0


' if textbox is blank - then ignore input
If TextBox1.value = "" Or TextBox1.value = 0 Then
    
    Exit Sub
End If


' loop to locate all batch numbers
On Error Resume Next
For Each c In sapcode
    
    If c.Text = TextBox1.Text Then
    s = 1
    Label2.Caption = c.Offset(0, 6).value
' loading up batch numbers if true
    
    coll.Add Item:=c.Offset(0, 3).Text, Key:=CStr(c.Offset(0, 3).Text)
    
        
      End If
Next c


' setting coll2 to coll - for batch checking later on
Set coll2 = coll




' if code not found returing error message
If s = 0 Then
    MsgBox "SAP code not found - please check and re-enter :) "
    reset
    
    Exit Sub
End If


' identifying materials with no batches
If coll.Count = 1 And coll.Item(1) = "" Then
nobatch
Set coll = Nothing
Exit Sub
End If






' setting up boxes etc
Label4.Visible = True
CommandButton2.Enabled = False
Label3.Visible = True
CommandButton1.Visible = False
TextBox2.Visible = False


' to stop it looping after focus being set
t = 1


' loading up batchs into combo box
With ComboBox1
    For Each var In coll
    .AddItem var
    Next var
    .Enabled = True
    .Visible = True
    .MatchEntry = fmMatchEntryComplete
    .SetFocus
End With


t = 1
' reset batch collection
Set coll = Nothing


End Sub

Re: Looping issue casuing slowdown on some computers

OK - So I have installed excel 2007 onto an old centrino CPU - it runs fine 5 seconds for the loop to run.

Installed 2007 (same excel program) - onto new I5 core laptop, 8 gig ram and the loop takes about 1 min. I'm now totally stumped

So is there a problem with I5 CPU's???

Is there a better way of running this loop??? - I have tried using a array but can't figure out how to check each cell and build the collection of batches for the combobox.

The joys of troubleshooting while actually doing the stocktake!!

Hi all - I've designed a tool that loops through a range of rows looking for a match - it's pretty simple really but for some reason on some comuters (running i5 processor 8gig ram) it really locks the system up. On my own compter (alienware m11x R1 - using low voltage CPU 1.3, video card turned off) it runs really fast.

OS's are both windows 7 - 64bit - I'm running excel 2007 32bit - and have also tested in citrix running excel 2010 64bit on my laptop

Code is:

VB:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
' to stop loops
If t = 1 Then
t = 0
Exit Sub
End If


' reset local worksheet
Sheets("sheet2").Select
Range("B1").Select
'set up range
Set sapcode = Range("B1", Range("B1").End(xlDown))
' clearing anythin in combobox
ComboBox1.Clear


s = 0


' if textbox is blank - then ignore input
If TextBox1.value = "" Or TextBox1.value = 0 Then
    
    Exit Sub
End If


' loop to locate all batch numbers
On Error Resume Next
For Each c In sapcode
    
    If c.Text = TextBox1.Text Then
    s = 1
    Label2.Caption = c.Offset(0, 6).value
' loading up batch numbers
    
    coll.Add Item:=c.Offset(0, 3).Text, Key:=CStr(c.Offset(0, 3).Text)
    
        
      End If
Next c


' setting coll2 to coll - for batch checking later on
Set coll2 = coll




' if code not found returing error message
If s = 0 Then
    MsgBox "SAP code not found - please check and re-enter :) "
    reset
    
    Exit Sub
End If

There is more but the loop problem is confined to the first loop - there are 67000 rows for it to run through normally this takes 2 or 3 seconds yet on some compters it's compleatly locking them up - I'm manually changed the range to sat 10,000 rows and this is still locking up some comuters (like the one I'm on) I'm at a compleate loss as to why - it runs fine on my laptop and a number of others - issue has just occurred today. I place the stop at the collection2 = collection1 to ensure I'm only looking at this loop and it's really weird??? why is this killing an I5 modern laptop

Re: Macro links date in first row to correct sequential blocs of forty days

No need for VB - you have not set a month so Date(year(1976),(no month),40) - it knows you have not specified a month and it knows there are 31 days in Jan - so it ignores the first 31 days and displays the balance which is 9 days.

just add the two cells together and set as a date format =A21 + 39 (thirty nine because your start date is the 2nd not the first)and you will get the correct date.

Re: Find all Cells containing a string that matches the string in a designated cell

You will need to declare a range - and run a for next loop through the range checking each cell to see if the text exists.

It's reasonably straight forward - I'm assuming you want the code to run after the combobox after exit or change - so something like

set myrange = range("A1", ("A1").end(xldown)) - (so this is looking for the last empty cell in column A1 but it could be range whatever)

for each c in myrange - checks each cell in your defined range

if instr(UCase(c.text), UCase("The cell your checking against")) > 0 then - looks for any matching text

do whatever - unsure what your trying to do if a match found

end if

next

Or you could do it using values through the loop - rather than using Instr

so - if c.value = range(whatever your cell is).value then

do whatever

end if
next

Re: VBA Lotus Notes - To attach instead of embed attachment

I did this many years ago - it may help

Re: Embedded ScrollBar

Hi Quickdraw - You can use both, so that when you scroll the value will change quickly, yet when you click on the up/down buttons the change is small. Also you can change the value of the smallchange value under the properities to say 10, if a unit of 1 is to small to speed up the up/down scroll speed

Re: Cell formula to move cursor when 2 cells equal

I think this is what you want???? - here is an example I've used the caculate event within the sheet to run the code.

Hope this helps - Phil

Re: Multiple attachments for LN

Hi Marxai - I think the problem lies with the generation of the array:

ie while you are trying to set up the attachmnet as Attachmnet11, it's not seeing the variable as that and is simply not hooking up an attachment.

the only suggestion I have is to specify the actual attachment path with a logic argumnet which destroys the simple bit of code you have generated.

i.e

For i = 1 To 3
'attachment = Attachment1(i)
if i = 1 then attachment = Attachment11
elseif i = 2 then attachment = Attachmnet12
elseif i = 3 then attachmnet = Attachmnet13
end if


Set attachME = MailDoc.CREATERICHTEXTITEM("attachment")
Set EmbedObj1 = attachME.EMBEDOBJECT(1454, "", attachment, "Attachment")
MailDoc.CREATERICHTEXTITEM ("Attachment")
Next i

not tested but should work, also not quite as pretty

Re: Sending email through Lotus from Excel

Hi Pandith - Here is an example that will run, will allow you to attach a file etc have used it in excel with no problems\.

It is baes around userforms but can be changed to suit whatever you are doing

Re: VBA - SubTotal Results of Auto Format

Hi Woodscanner - normally if I'm trying to find any empty cell within a row, I simply run a loop thought the range of cells that may contain data looking for the empty cell. Once found simply add a formula within that cell.

ie

dim myrange as range
dim c


set my range = range("whatever it is")


for each c in myrange


if isempty(c.value) then
' your formula here
end if
next

If you then need to know how many cells there are before finding the empty one you will need to run a counter to determine the number of cells the loop has run through so that your forumula can include this in the caculation.

like so

Dim myrange As Range
    Dim c
    Dim counter, sumall As Integer
    
    
    Set myrange = Range("F16:F46")
    counter = 16
    For Each c In myrange
    sumall = sumall + c.Value
    If IsEmpty(c.Value) Then
        c.Value = sumall
    Exit Sub
    End If
    Next

hope this helps - Phil

Re: Winzip

Hi Naganesh - What you want is very complex, firstly there is no function that does what you want, however you can break this into two routines.

The first to zip the file using code written by Will R - a brillant coder, see the first attached file taken from the that's cool section.

The second is harder as people will and do use different e-mail clients, are two main ones Outlook, & Lotus Notes - Each requires different code to run. Here is an example of code for Lotus notes that will hook up an attachment currently it looks for excel spreadsheets but it's simple to fix this. I have not tested or used outlook so I can't help further but this may get you started.

Phil

Opps - file was from Richie UK - my mistake, link below takes you to the same file - However there a few masters here, Me I'm still learning.

Re: change color of cell toolbar

Not sure if this is what you were looking for but the open event is under:

ThisWorkbook - click on the left tab which should say general, from the dropdown select Workbook - and in the left had tab are the various option events for the workbook, one of them is open - this is the event triggered when the workbook is first opened and will run the code specified.

Hope this helps - Phil

Re: Input Box

Hi Abbeville - You can add information into your inputbox from your spreadsheet like this:

returnvalue = InputBox("whatever you want" & sheets("sheet1").range("myrange").value, "Information")

To add a counter you simply need to add a + argument

dim counter as integer


if returnvalue = "1" then
counter = sheets("sheet1").range("myrange").value +1
sheets("sheet1").range("myrange").value = counter
application.run "newseason1"

This simply adds one extra value to the sheet selected - hope this helps Phil

Re: VBA: Matching TextBox Pairs

Hi Arve - Enjoy the beer!!!

One option for you is to populate a dropdown box based on the product selected so that you don't need to run any if then arguments. Simply define the product pack size for each product and populate the dropdown box for them to select it from.

Hope this helps if not........have another drink for me!!!!

Re: for next loop

Hi Batman - Bloody good idea!!!! - Never would have thought of it using the cell type to control the loop now that's what I call thinking!!!!!!!

Beats running a counter to control the loop, dam clever - I won't forget this one in a hurry.

Thanks - call this one solved!!!

Re: Macro and security level

Sorry Jtang - I should have explained it better, but Derk is 100% correct (that's why hes the professor and we mortals are still in school!!!).

The idea it to trick the user into changing their security level which by default is on high, the sheets are xlveryhidden, which means they can't be un hidden using the normal formatt => sheet => unhide, this tricks the average user and also makes it hard for most advanced users if the code is password protected also.

And it clearly worked for you, as you did not even see the display sheet!!!!!!