lock combination of cells

Hopefully my last question for this spreadsheet!!

I have a spreadsheet that calculates the effect of weight on a balance
wherever the weight is placed.
I now want to be able to place some weights where I want them rather
than have the spreadsheet calculate all the options for me.
To explain say there are 4 weights a,b,c,d and 4 positions 1,2,3,4
I can calculate all the permutations for the effect of these weights in
all positions and return the optimum balance for me BUT i want weight
"a" to be in position 1.(assuming position 1 address is a2) and return
the optimum balance with this given condition.
I guess for a function I can use =if(a2=1,a2*ref arm,a2*999999)
a2*999999 would return an unfavourable result andtherefore not be
selected as the optimum.
My question is how can i create the input form for the user to
stipulate which weight should be where so it is user friendly?
cheers
Nobby


--
nobbyknownowt

Hi Nobbyknownowt,
I try to avoid userforms if I can get away with it. If you feel the
same way you could use the following series of application.inputboxes
with the input data type restricted to range.
This way is very user friendly because he/she is confronted with one
question at a time; also, they don't have to type in a cell address,
they can input the position simply by clicking on the appropriate cell.
It's also developer friendly, user forms take longer to develop.
The only disadvantage is that the user has to do a bit more mouse
clicking, so if RSI is a problem read no further ;-)

Public Sub weight_positions()
Dim rgW1 As Range 'weight a's cell position
Dim rgW2 As Range 'weight b's cell position
Dim rgW3 As Range 'weight c's cell position
Dim rgW4 As Range 'weight d's cell position
Dim Wa As Single 'I have used 10 g
Dim Wb As Single 'I have used 15 g
Dim Wc As Single 'I have used 25 g
Dim Wd As Single 'I have used 50 g
Wa = 10: Wb = 15: Wc = 25: Wd = 50
Set rgW1 = Application.InputBox(prompt:= _
"Where should the " & Wa & "g weight be?" _
& Chr(10) & "(Click Cell)", _
Type:=8)
Set rgW2 = Application.InputBox(prompt:= _
"Where should the " & Wb & "g weight be?" _
& Chr(10) & "(Click Cell)", _
Type:=8)
Set rgW3 = Application.InputBox(prompt:= _
"Where should the " & Wc & "g weight be?" _
& Chr(10) & "(Click Cell)", _
Type:=8)
Set rgW4 = Application.InputBox(prompt:= _
"Where should the " & Wd & "g weight be?" _
& Chr(10) & "(Click Cell)", _
Type:=8)
MsgBox "a at " & rgW1.Address(False, False) & Chr(10) _
& "b at " & rgW2.Address(False, False) & Chr(10) _
& "c at " & rgW3.Address(False, False) & Chr(10) _
& "d at " & rgW4.Address(False, False) & Chr(10)
End Sub

The msgbox on the last line is just to show you that the code works by
showing the selected cell addresses. Just replace that with your code.

If you also want the user to input the weight values you could include
Wa = Application.InputBox(prompt:="Weight a grams=?", Type:=1)
Wb = Application.InputBox(prompt:="Weight b grams=?", Type:=1)
Wc = Application.InputBox(prompt:="Weight c grams=?", Type:=1)
Wd = Application.InputBox(prompt:="Weight d grams=?", Type:=1)

either all together so that all the weights are first entered, or
position each one before its corresponding cell range input.

Ken Johnson

PS your spreadsheet sounds interesting. If you could send me a copy
when you've finished it would be appreciated. I teach high school
science so it could be useful.

Hi Ken
Thanks for the reply and sorry for not replying sooner, I posted two
questions together and only the other one comes up when I use the
search facility so this message takes longer to find.
I understand where you are coming from here and will certainly
experiment with it but I dont think it would be suitable for my
purpose. (The spreadsheet is a balance program for an aircraft). I only
need to lock a couple of cell positions for say something that
specifically must be at the front of the aircraft or something at the
back and so on.
I want the user to be able to input a list of up to 20 weights they
have in any order in a list, be able to specify if anything should be
loaded in a specific place, then they activate the macro. The macro
then calculates the effect of every weight in every position on the
aircraft and reurns the optimum loading peramiters. This data then
transfers to a seperate spreadsheet that produces (I hope) the flight
paperwork. The only way the program will be used is if it is simple to
operate.
Not a problem to send you a copy once I have completed it (although I
been working at it on and off for a few months now, just when you think
you are there another problem crops up!!)

cheers
Nobby


--
nobbyknownowt

Hi Nobby,
Thanks for the feedback.
How will the user be inputting the data, directly into cells on the
sheet or are you wanting to set up a userform?
If inputs are on the sheet you could have one column devoted to the
weight and the next column for the position. You could use data
validation to make a dropdown list of all the possible positions
including an "anywhere" option. Then change your macro so that it works
out the optimum positions for all the "anywhere" weights with the other
weights in their fixed positions.
Or (you'll more than likely laugh at this one) there could be an
outline drawing of the plane (no fill) on the sheet with the cells
corresponding to possible weight positions indicated somehow eg border
or fill color. These cells could also be unlocked so that with all
other cells locked and the sheet protected they are the only cells the
user can edit. (If your excel version is not too old the protection
dialog also offers the choice of restricting cell selection to those
unlocked cells, otherwise you have to edit the worksheet's properties
in the VBA editor).
The user could then type the various weights into these unlocked cells
inside the plane diagram and could indicate a fixed position with bold
font. Your optimising macro then has to test the font style (Bold or
Regular) using something like...

If ActiveSheet.Cells(i, j).Font.Bold = True Then
'This weight has fixed position
Else: 'This weight can be moved to other positions
End If

(The SpecialCells method would probably be easier to code than nested
loops, I've just forgotten the details)

To make it as easy as possible for the user to control the Bold/Regular
font style the sheet could have a BeforeDoubleClick event sub (or
BeforeRightClick) in its code module...

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
Target.Font.Bold = Not Target.Font.Bold
End Sub

Or...

Private Sub Worksheet_BeforeRightClick _
(ByVal Target As Range, Cancel As Boolean)
Cancel = True 'stops the usual popup from appearing
Target.Font.Bold = Not Target.Font.Bold
End Sub

This way, every time the user double clicks a cell (or right clicks,
depending on which one is used) its Bold/Regular style changes.

I don't work with userforms often enough to be able to offer any
suggestions for that type of solution. Also, more detailed information
about your project would be needed before such a solution could be
attempted.

Hope you get it working the way you want.
Looking forward to receiving a copy when you're ready.

Ken Johnson